MySQL USE INDEX Hint

Created with Sketch.

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:

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

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%';

Code 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)

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:

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

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:

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 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.

Leave a Reply

Your email address will not be published. Required fields are marked *