MySQL Index Hint：USE INDEX

This article describes how to use the USE INDEX to recommend MySQL query optimizer use specified named indexes.

The MySQL query optimizer is a component of the MySQL database server that formulates the best execution plan for SQL statements. The MySQL optimizer usually bases its decisions on index cardinality. Sometimes, although you have created an index, your SQL statement does not necessarily use the index. This is because the MySQL query optimizer made what it thought was a better choice.

MySQL allows you to use the USE INDEX clause to advise the query optimizer to use specific named indexes.

However, it is still possible that the MySQL query optimizer does not apply the index you suggest. If you want MySQL to use the index you specify, use the FORCE INDEX clause.

If the query optimizer is using a wrong index, USE INDEX is useful.

MySQL USE INDEX syntax

To give an index hint with MySQL USE INDEX, follow the syntax:

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

Explanation:

  • Put the USE INDEX clause after the FROM clause.
  • If the MySQL query optimizer is going to use an index, it must use one of the indexes in the index_list list.

Note that the query optimizer will not necessarily use the named index you suggest.

MySQL USE INDEX example

We’ll use the customer table from the Sakila sample database for demonstration.

Take a look at the definition of the actor table:

DESC customer;

+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type              | Null | Key | Default           | Extra                                         |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| customer_id | smallint unsigned | NO   | PRI | NULL              | auto_increment                                |
| store_id    | tinyint unsigned  | NO   | MUL | NULL              |                                               |
| first_name  | varchar(45)       | NO   |     | NULL              |                                               |
| last_name   | varchar(45)       | NO   | MUL | NULL              |                                               |
| email       | varchar(50)       | YES  |     | NULL              |                                               |
| address_id  | smallint unsigned | NO   | MUL | NULL              |                                               |
| active      | tinyint(1)        | NO   |     | 1                 |                                               |
| create_date | datetime          | NO   |     | NULL              |                                               |
| last_update | timestamp         | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
9 rows in set (0.00 sec)

Let’s create two indexes,

CREATE INDEX idx_last_name
ON customer (last_name);

CREATE INDEX idx_last_name_first_name
ON customer (last_name, first_name);

Take a look at the indexes in the customer table:

SHOW INDEXES FROM customer;

+----------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name                 | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| customer |          0 | PRIMARY                  |            1 | customer_id | A         |         599 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| customer |          1 | idx_fk_store_id          |            1 | store_id    | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| customer |          1 | idx_fk_address_id        |            1 | address_id  | A         |         599 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| customer |          1 | idx_last_name            |            1 | last_name   | A         |         598 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| customer |          1 | idx_last_name_first_name |            1 | last_name   | A         |         598 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| customer |          1 | idx_last_name_first_name |            2 | first_name  | A         |         599 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
6 rows in set (0.00 sec)

Use the EXPLAIN statement to view the execution plan of the following statement whose last name is BARBEE:

EXPLAIN
SELECT *
FROM customer
WHERE last_name = 'BARBEE';

+----+-------------+----------+------------+------+----------------------------------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys                          | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+----------------------------------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | customer | NULL       | ref  | idx_last_name,idx_last_name_first_name | idx_last_name | 182     | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+----------------------------------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

As you can see from the output, the MySQL query optimizer chose to use the idx_last_name index.

If you think it is better to use idx_last_name_first_name, specify it using USE INDEX, as follows:

EXPLAIN
SELECT *
FROM customer
USE INDEX (idx_last_name_first_name)
WHERE last_name = 'BARBEE';

+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys            | key                      | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | customer | NULL       | ref  | idx_last_name_first_name | idx_last_name_first_name | 182     | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Note that this is for demonstration purposes only, and not optimal.

As you can see from the output, the MySQL query optimizer chose to use the idx_last_name_first_name index.

Conclusion

This article discusses the basic usage of MySQL USE INDEX index hints. USE INDEX is different from FORCE INDEX:

  • USE INDEX tell MySQL to use one of the indexes in the list for this query, but MySQL may not use it.
  • FORCE INDEX force MySQL to use a specific index.