8.9.2 Index Hints

Index hints give the optimizer information about how to choose indexes during query processing. Index hints are specified following a table name. (For the general syntax for specifying tables in a SELECT statement, see Section, “JOIN Syntax”.) The syntax for referring to an individual table, including index hints, looks like this:

tbl_name [[AS] alias] [index_hint]

    USE {INDEX|KEY} [FOR JOIN] (index_list)
  | IGNORE {INDEX|KEY} [FOR JOIN] (index_list)
  | FORCE {INDEX|KEY} [FOR JOIN] (index_list)

    index_name [, index_name] ...

The USE INDEX (index_list) hint tells MySQL to use only one of the named indexes to find rows in the table. The alternative syntax IGNORE INDEX (index_list) tells MySQL to not use some particular index or indexes. These hints are useful if EXPLAIN shows that MySQL is using the wrong index from the list of possible indexes.

The FORCE INDEX hint acts like USE INDEX (index_list), with the addition that a table scan is assumed to be very expensive. In other words, a table scan is used only if there is no way to use one of the named indexes to find rows in the table.

Each hint requires the names of indexes, not the names of columns. To refer to a primary key, use the name PRIMARY. To see the index names for a table, use SHOW INDEX.

An index_name value need not be a full index name. It can be an unambiguous prefix of an index name. If a prefix is ambiguous, an error occurs.

USE INDEX, IGNORE INDEX, and FORCE INDEX affect only which indexes are used when MySQL decides how to find rows in the table and how to do the join. They do not affect whether an index is used when resolving an ORDER BY or GROUP BY clause. As of MySQL 5.0.40, the optional FOR JOIN clause can be added to make this explicit.


SELECT * FROM table1 USE INDEX (col1_index,col2_index)
  WHERE col1=1 AND col2=2 AND col3=3;

SELECT * FROM table1 IGNORE INDEX (col3_index)
  WHERE col1=1 AND col2=2 AND col3=3;

For FULLTEXT searches, index hints do not work before MySQL 5.0.74. As of 5.0.74, index hints work as follows:

  • For natural language mode searches, index hints are silently ignored. For example, IGNORE INDEX(i1) is ignored with no warning and the index is still used.

  • For boolean mode searches, index hints are honored.

User Comments
  Posted by Igor Ivoilov on March 27, 2008
If you need to make select from 2 tables the syntax is

SELECT table1.*,table2.* FROM table1 USE INDEX (col2_index), table2 WHERE table1.col1=table2.col1 AND table1.col2=2 AND table1.col3=3;
  Posted by David Bennett on May 21, 2008
Due to an object relational layer that doesn't allow the specification of the index hint in the table_references declaration, I was forced into discovering an alternate technique. By specifying an indexes first column as the first where_condition I was able to force the optimizer to use the index that I wanted. In this case the index I wanted to use began with the 'edited' column (a datetime).

select * from appointment where edited < now() and ...

changed the optimizers index selection and greatly improved query performance.

  Posted by Ilan Hazan on November 15, 2010
The query analyzer is not perfect. An Optimizer that picks the wrong plan can cause severe performance issues.
However, this does NOT mean that you should always use a Force Index hint.
Read more at http://www.mysqldiary.com/the-battle-between-force-index-and-the-query-optimizer/

Sign Up Login You must be logged in to post a comment.