MySQL USE INDEX Hint

Summary: in this tutorial, you will learn how to use the MySQL USE INDEX hint to 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 to the database server, the query optimizer attempts to create an optimal query execution plan.

To determine the best possible plan, the query optimizer relies on several parameters. One of the most crucial parameters for selecting the appropriate index is stored key distribution, also known as cardinality.

The cardinality, however, may be not accurate. For example, if the table has been modified heavily with many inserts or deletes, the cardinality is not updated timely.

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

In addition, MySQL allows you to recommend the indexes that the query optimizer should consider using an index hint.

Here’s the basic syntax for using the USE INDEX hint:

SELECT select_list
FROM table_name USE INDEX(index_list)
WHERE condition;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 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:

SHOW INDEXES FROM customers;Code language: SQL (Structured Query Language) (sql)

Second, create four indexes as follows:

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);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:

EXPLAIN SELECT *
FROM
    customers
WHERE
    contactFirstName LIKE 'A%'
        OR contactLastName LIKE 'A%'\GCode language: SQL (Structured Query Language) (sql)

The following shows the output of the statement:

           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)
Code language: SQL (Structured Query Language) (sql)

The output indicates that 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:

EXPLAIN SELECT *
FROM
    customers
USE INDEX (idx_name_fl, idx_name_lf)
WHERE
    contactFirstName LIKE 'A%'
        OR contactLastName LIKE 'A%'\GCode language: SQL (Structured Query Language) (sql)

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

The following illustrates the output:

           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)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 can be useful if the query optimizer uses the wrong index from the list of possible indexes.

Summary

• Use the MySQL USE INDEX hint to instruct the query optimizer to use the only list of specified indexes.