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 theUSE INDEX
clause. - The key column has both
idx_name_fl
andidx_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.