Using Indexes in SQL

How to Create an Index?

Indexes are special data structures used to speed up data queries by pointing to the desired column data (pointer) in the table. In the following code, an index has been created for the index_col1 column of the table_name table with the name index_name.

Our data is marked and stored in an ordered manner according to the index structure we defined. When we query through our index, we perform a read operation over the indexes, not over all data, resulting in faster data reading.

To delete a created index, use the following command:

When Should an Index Be Used?

·       If queries are returning responses slower than expected,

·       If there is a column frequently used in conditions in queries,

·       If a column contains a wide range of values,

·       If a column does not contain many NULLs,

·       If one or more columns frequently use WHERE or join operations together.

Scanning the entire table slows down the query as the table structure grows and takes more time to reach the result. To solve the time cost issue in such query operations, indexing is used.

Things to Consider When Using Indexes

We should be cautious of unnecessary index usage because it will create an additional load. Adding an extra step when writing data reduces our write speed.When the read rate is lower than the write rate, removing indexes eliminates unnecessary load and performance loss.

Tables with extensive index usage put an extra load on the database since indexing takes up space on disk. This is something to consider when tables grow large.

How Does an Index Work?

If we need to query with the "email" column in a "user" table with 100,000 records, it needs to scan the entire "email" column when we create the query.

When a query operation is performed with an index on the "email" column, the query process is executed using an ordered expression as shown below.

As the amount of data increases, read operations increase without indexing, while with indexing, the read operations increase only slightly as the data doubles, keeping the read count low relative to the data.

Practical Use of Indexes in SQL

When a SQL table is created without setting the ID value as the primary key, it applies a table scan by scanning random pages as seen in the image.

When a primary key is given, it performs a clustered index scan.

Without using an index, a name search in a table with 100,000 records required reading 1965 pages, and as each page is 8 bytes, it resulted in a 15351 KB read.

Considering that the "SP_SPACEUSED ‘table_name’" command shows a total usage of 15752 KB, it can be evaluated that almost all data was read.

In the code below, an index named IX1 is created on the FirstName column.

Running the same select query resulted in 6 page reads, achieving 327 times faster reading

While obtaining all information in the row where the name was found as a result of the name search, an additional key lookup operation was performed with indexing. Key Lookup is the process of retrieving the name, then obtaining the ID of that name, followed by fetching information belonging to that ID

By adding included columns to our created index, we prevent re-performing the key lookup operation, allowing it to return all information already available in the index, resulting in faster operation with fewer page reads.

In summary, when used on the correct column with the correct included column options, indexing significantly improves and speeds up query performance.