Skip navigation links

Additional languages
12.2.8.3 UNION Syntax »
Section Navigation      [Toggle]

12.2.8.2. Index Hint Syntax

You can provide hints to give the optimizer information about how to choose indexes during query processing. Section 12.2.8.1, “JOIN Syntax”, describes the general syntax for specifying tables in a SELECT statement. The syntax for an individual table, including that for index hints, looks like this: 

tbl_name [[AS] alias] [index_hint_list]

index_hint_list:
    index_hint [, index_hint] ...

index_hint:
    USE {INDEX|KEY}
      [{FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | IGNORE {INDEX|KEY}
      [{FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
  | FORCE {INDEX|KEY}
      [{FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

index_list:
    index_name [, index_name] ...

By specifying USE INDEX (index_list), you can tell MySQL to use only one of the named indexes to find rows in the table. The alternative syntax IGNORE INDEX (index_list) can be used to tell 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.

You can also use FORCE INDEX, which acts like USE INDEX (index_list) but 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 given indexes to find rows in the table.

Each hint requires the names of indexes, not the names of columns. The name of a PRIMARY KEY is 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 given that is ambiguous, an error occurs.

Index hints do not work for FULLTEXT indexes.

Examples: 

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;

The syntax for index hints has the following characteristics:

  • It is syntactically valid to specify an empty index_list for USE INDEX, which means “use no indexes.” Specifying an empty index_list for FORCE INDEX or IGNORE INDEX is a syntax error.

  • You can specify the scope of a index hint by adding a FOR clause to the hint. This provides more fine-grained control over the optimizer's selection of an execution plan for various phases of query processing. To affect only the indexes used when MySQL decides how to find rows in the table and how to process joins, use FOR JOIN. To influence index usage for sorting or grouping rows, use FOR ORDER BY or FOR GROUP BY. (However, if there is a covering index for the table and it is used to access the table, the optimizer will ignore IGNORE INDEX FOR {ORDER BY|GROUP BY} hints that disable that index.)

  • You can specify multiple index hints: 

    SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX FOR ORDER BY (i2) ORDER BY a;

    It is not a error to name the same index in several hints (even within the same hint): 

    SELECT * FROM t1 USE INDEX (i1) USE INDEX (i1,i1);

    However, it is an error to mix USE INDEX and FORCE INDEX for the same table: 

    SELECT * FROM t1 USE INDEX FOR JOIN (i1) FORCE INDEX FOR JOIN (i2);

if you specify no FOR clause for an index hint, the hint by default applies to all parts of the statement. For example, this hint: 

IGNORE INDEX (i1)

is equivalent to this combination of hints: 

IGNORE INDEX FOR JOIN (i1)
IGNORE INDEX FOR ORDER BY (i1)
IGNORE INDEX FOR GROUP BY (i1)

To cause the server to use the older behavior for hint scope when no FOR clause is present (so that hints apply only to row retrieval), enable the old system variable at server startup. Take care about enabling this variable in a replication setup. With statement-based binary logging, having different modes for the master and slaves might lead to replication errors.

When index hints are processed, they are collected in a single list by type (USE, FORCE, IGNORE) and by scope (FOR JOIN, FOR ORDER BY, FOR GROUP BY). For example: 

SELECT * FROM t1
  USE INDEX () IGNORE INDEX (i2) USE INDEX (i1) USE INDEX (i2);

is equivalent to: 

SELECT * FROM t1
   USE INDEX (i1,i2) IGNORE INDEX (i2);

The index hints then are applied for each scope in the following order:

  1. {USE|FORCE} INDEX is applied if present. (If not, the optimizer-determined set of indexes is used.)

  2. IGNORE INDEX is applied over the result of the previous step. For example: 

    SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX (i2) USE INDEX (i2)

    is equivalent to: 

    SELECT * FROM t1 USE INDEX (i1).

User Comments

Posted by Igor Ivoilov on March 27 2008 7:51am[Delete] [Edit]

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 4:21pm[Delete] [Edit]

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.

Add your own comment.