In this tutorial, we will learn about the USE INDEX for giving hints to the query optimizer to use only a given list of indexes for that query. It’s super easy to understand as well as implement. So, let’s get started!
Also read: MySQL Invisible Index
Introduction to MySQL USE INDEX
Indexes provide you with a great way to optimize the query performance to give you records in minimum time. This process is taken care of by the system internally to choose which index to use for optimal query execution.
Many factors are taken into consideration while choosing the best possible way of optimal query execution. Of which, cardinality is one of the important factors.
Cardinality simply means the uniqueness of the data values that can be inserted into columns.
Also read: MySQL Prefix Index with Examples
However, due to multiple inserts, updates and deletes operations, the cardinality might change. You can execute the ANALYZE TABLE statement periodically to update the cardinality.
Along with that, MySQL offers you the way to recommend the query optimizer to use the particular index hint which is called the USE INDEX hint.
Using the USE INDEX statement, you can specify one or multiple indexes which will give the hint to the query optimizer to use them to find the records from the table.
MySQL USE INDEX Statement
The following syntax shows how to use the USE INDEX hint statement with the query.
SELECT cols_list
FROM table_name USE INDEX(index_list)
WHERE condition;Code language: SQL (Structured Query Language) (sql)Here, the index_list might be only one index or a group of multiple indexes.
Note that, even if you recommend using the indexes, it totally depends on the query optimizer to decide to use the given indexes or not depending on the usage of that indexes.
For example, if the two indexes are set on the columns col1 and col2. You try to fetch the data by using the where clause on the col1 and you give the hint to use the index of col2 then the query optimizer will reject your hint as it will not help in any way for optimal execution.
MySQL USE INDEX Examples
To demonstrate the examples of the USE INDEX, we will use the following table schema and data.
Now we will create four indexes on the table emps.
CREATE INDEX ind_e_name ON emps(name);
CREATE INDEX ind_e_city ON emps(city);
CREATE INDEX ind_e_nc ON emps(name,city);
CREATE INDEX ind_e_cn ON emps(city,name);Code language: SQL (Structured Query Language) (sql)
Now, we will write a query to fetch the records where either name or city starts with the letter ‘a’. We will use the explain clause to check which indexes are being used to find the records.
EXPLAIN SELECT * FROM emps WHERE name LIKE 'a%' OR city LIKE 'a%' \G;Code language: SQL (Structured Query Language) (sql)
Here you can see, that the query optimizer has used the index ind_e_nc, however, the possible keys can be any of the four indexes that we created.
Note that, you can recommend the query optimizer use different indexes to use if you think the default solution is not good.
EXPLAIN SELECT * FROM emps
USE INDEX(ind_e_name,ind_e_city)
WHERE name LIKE 'a%' OR city LIKE 'a%' \G;Code language: SQL (Structured Query Language) (sql)
As you can see here, we have recommended the query optimizer use different indexes which are then used to find the records.
Also, you can find the indexes you specified with the USE INDEX clause in the possible_keys column. However, if the given indexes are used to find the records, those will be listed in the key column.
Now, we will try to specify the different index names to see if the query optimizer uses them or not.
EXPLAIN SELECT * FROM emps
USE INDEX(ind_e_name)
WHERE name LIKE 'a%' OR city LIKE 'a%'\GCode language: SQL (Structured Query Language) (sql)
As you can see, the index that we specify is not helpful to find the records from the table, that’s why the query optimizer doesn’t use it.
Conclusion
In this tutorial, we have learned about the USE INDEX statement. We learned how to use the USE INDEX to give the recommendation to the query optimizer to use specific indexes. I hope this tutorial helped you to learn something new. If it did, don’t forget to share it with your friends, because sharing is caring!
References
MySQL official documentation on index hints.