Summary: in this tutorial, you will learn how to use the MySQL USE INDEX hint instruct the query optimizer to use only a list of named indexes for a query.

Introduction to MySQL USE INDEX hint

In MySQL, when you submit an SQL query, the query optimizer will try to make an optimal query execution plan.

To determine the best possible plan, the query optimizer makes use of many parameters. One of the most important parameters for choosing which index to use is stored key distribution which is also known as cardinality.

The cardinality, however, may be not accurate for example in case the table has been modified heavily with many inserts or deletes.

To solve this issue, you should run the ANALYZE TABLE statement periodically to update the cardinality.

In addition, MySQL provides an alternative way that allows you to recommend the indexes that the query optimizer should by using an index hint called USE INDEX.

The following illustrates syntax of the MySQL USE INDEX hint:

Code language: SQL (Structured Query Language) (sql)

In this syntax, the USE INDEX instructs the query optimizer to use one of the named indexes to find rows in the table.

Notice that when you recommend the indexes to use, the query optimizer may either decide to use them or not depending on the query plan that it comes up with.

MySQL USE INDEX example

We will use the customers table from the sample database for the demonstration.

First, use the SHOW INDEXES statement to display all indexes of the  customers table:

Code language: SQL (Structured Query Language) (sql)

Second, create four indexes as follows:

Code language: SQL (Structured Query Language) (sql)

Third, find customers whose contact first name or contact last name starts with the letter A. Use the EXPLAIN statement check which indexes are used:

Code language: SQL (Structured Query Language) (sql)

The following shows the output of the statement:

Code language: SQL (Structured Query Language) (sql)

As you can see, the Query Optimizer used the idx_c_fn and idx_c_ln indexes.

Fourth, if you think that it is better to use the idx_c_fl and idx_c_lf indexes, you use the USE INDEX clause as follows:

Code language: SQL (Structured Query Language) (sql)

Notice that this is just for the demonstration purposes, not the best choice though.

The following illustrates the output:

Code language: SQL (Structured Query Language) (sql)

These are the changes:

• The possible_keys column only lists the indexes specified in the USE INDEX clause.
• The key column has both idx_name_fl and idx_name_lf. It means that the Query Optimizer used the recommended indexes instead.

The USE INDEX is useful in case the EXPLAIN shows that the Query Optimizer uses the wrong index from the list of possible indexes.

In this tutorial, you have learned how to use the MySQL USE INDEX hint to instruct the Query Optimizer to use the only list of specified indexes to find rows in a table.