Database — How to use the index in the SQL query

Indexes could be used for enhancing the SQL query performance, and user can build multiple indexes for a table. Then, how the query engine picks up the index from multiple choices. Let me share some ideas in this post.

There is a query optimizer in the query engine, it is responsible to pick up the most suitable index for the query. In some cases, it’s also possible that the optimizer decides to traverse the whole table rather than using any index.

The Mysql database allows users to give some index hints to the query optimizer by the keyword USE INDEX.

SELECT * FROM my_table USE INDEX (my_index) WHERE a=1

Moreover, users can even force the optimizer to use a specific index by the keyword FORCE INDEX.

SELECT * FROM my_table FORCE INDEX (my_index) WHERE a=1

However, to make sure the optimizer could use the expecting index correctly, you must follow the leftmost prefix rule. That means the optimizer will try to match the columns in the index from left to right one by one. Let’s go through some examples. Assume we create an index by the command.

CREATE INDEX my_index ON my_table (a, b, c);

The following shows the cases of whether the index could be referred under the queries.

The index is one of the most important factors when tuning the SQL performance. Write the SQL carefully to make sure it could refer to the index you expected.