Download
1 / 9
100 likes | 111 Views
Querying information is an expensive process, and a database administrator always aims to reduce query execution time. Database Indexes are powerful tools that help improve query performance significantly. In todayu2019s PPT, we will explore the SQL index and how they work under the hood.
E N D
The speed of information retrieval from a relational database is inversely proportional to the amount of data. The primary reasons for slow running queries are either lack of indexes or bad indexing strategies. Querying information is an expensive process, and a database administrator always aims to reduce query execution time. Database Indexes are powerful tools that help improve query performance significantly. In today’s PPT, we will explore the SQL index and how they work under the hood.
An Introduction to Database Indexes • Database Indexes are data stores that contain key-value pairs that help in faster data retrieval from the database. The keys are usually a column in a table, and the values are the location ( what we call an index) of where that value is present in the table. • There can be one or more indexes created for each table based on the columns selected. However, creating an index is not an easy process. It’s a tradeoff between speed and storage. While they do increase information retrieval speeds, they take a lot more storage space, and they also incur an index writing cost. As data increases, these costs can become overwhelming to handle.
Clustered and Non-Clustered Indexes in databases • Clustered and non-clustered indexes are the most common indexes found in popular databases. Before we delve into the differences, there is one thing common between them. Both of these indexes need to be unique. The repetition of keys in an index is not allowed. Secondly, the database server maintains the database indexes. When data in a table changes, the indexes are automatically updated. • In a clustered index, the data rows are stored in sorted order. The index definition includes specific columns, and the value of these columns is in sorted order. The data in a table can only be stored in one sorted order, and because of this, we can have only one clustered index per table. • The non-clustered indexes are not sorted. They are a collection of key-value pairs, where each pair points to the actual row in the table. The pointers are called row locators, and their structure depends on the created indexes. For non-clustered indexes, they are direct pointers to the location of the row in the table.
Creating a SQL Index • With a fair understanding of what indexes are, we can now look at creating a SQL Index. The process of creating indexes varies based on the database we use. To start with, let’s assume we have a table called “employee” with the following structure: Now, let’s assume that we need to create an index for the Name column. We will look at the Syntax for the same. For the MySQL and Oracle database indexes, we can create an index using the “CREATE INDEX” SQL query.
When should you create Indexes? • The creation of database indexes is not always a straightforward task. As we discussed earlier, creating indexes is a tradeoff between speed on the one hand and storage space, and DB writes on the other. So, here are some tips you can use to decide when to create an index: Tip #1 - Slow Queries We have been performing database optimizations for almost two decades, and the lack of proper indexes is one of the most found reasons for slow queries. There are many tools to identify slow queriesbut very few tools to help you fix the index issues that cause it. Once you have identified your slow queries, you can then look at the accessing tables, followed by the columns in that same table. This gives a starting point on all the columns that need to be indexed.
Tip #2 - Large Database Volume When the volume of data increases in the database, almost all the queries will start running slow. Searching for data in vast amounts of data takes time, and creating indexes on tables with large amounts of data is the way to go.
Optimize Any Slow SQL Query In Seconds With a DBA 2.0 Approach • Visit at OptimizSQL,Our platform has been developed with a unique approach by including an analytic intelligence with predictive and analytic algorithms with several scenarios in order to propose an advanced indexing strategy quickly.
