MySQL USE INDEX Hint

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:

1 2 3

SELECT select_list FROM table_name USE INDEX(index_list) WHERE condition;

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:

1	SHOW INDEXES FROM customers;

Second, create four indexes as follows:

1 2 3 4

CREATE INDEX idx_c_ln  ON customers(contactLastName); CREATE INDEX idx_c_fn ON customers(contactFirstName); CREATE INDEX idx_name_fl  ON customers(contactFirstName,contactLastName); CREATE INDEX idx_name_lf  ON customers(contactLastName,contactFirstName);

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:

1 2 3 4 5 6

EXPLAIN SELECT * FROM     customers WHERE     contactFirstName LIKE 'A%'         OR contactLastName LIKE 'A%';

The following shows the output of the statement:

1 2 3 4 5 6 7 8 9 10 11 12 13

id: 1   select_type: SIMPLE         table: customers    partitions: NULL          type: index_merge possible_keys: idx_c_ln,idx_c_fn,idx_name_fl,idx_name_lf           key: idx_c_fn,idx_c_ln       key_len: 52,52           ref: NULL          rows: 16      filtered: 100.00         Extra: Using sort_union(idx_c_fn,idx_c_ln); Using where 1 row in set, 1 warning (0.00 sec)

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:

1 2 3 4 5 6 7

EXPLAIN SELECT * FROM     customers USE INDEX (idx_name_fl, idx_name_lf) WHERE     contactFirstName LIKE 'A%'         OR contactLastName LIKE 'A%';

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

The following illustrates the output:

1 2 3 4 5 6 7 8 9 10 11 12 13

id: 1   select_type: SIMPLE         table: customers    partitions: NULL          type: index_merge possible_keys: idx_name_fl,idx_name_lf           key: idx_name_fl,idx_name_lf       key_len: 52,52           ref: NULL          rows: 16      filtered: 100.00         Extra: Using sort_union(idx_name_fl,idx_name_lf); Using where 1 row in set, 1 warning (0.00 sec)

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.