Movatterモバイル変換

MySQL 9.5 Reference Manual

10.9.4 Index Hints

Index hints give the optimizer information about how to choose indexes during query processing. Index hints, described here, differ from optimizer hints, described inSection 10.9.3, “Optimizer Hints”. Index and optimizer hints may be used separately or together.

Index hints apply toSELECT andUPDATE statements. They also work with multi-tableDELETE statements, but not with single-tableDELETE, as shown later in this section.

Index hints are specified following a table name. (For the general syntax for specifying tables in aSELECT statement, seeSection 15.2.13.2, “JOIN Clause”.) The syntax for referring to an individual table, including 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|FORCE} {INDEX|KEY}      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)index_list:index_name [,index_name] ...

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

TheFORCE INDEX hint acts likeUSE INDEX (index_list), with the addition that a table scan is assumed to bevery 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.

Note

MySQL 9.5 supports the index-level optimizer hintsJOIN_INDEX,GROUP_INDEX,ORDER_INDEX, andINDEX, which are equivalent to and intended to supersedeFORCE INDEX index hints, as well as theNO_JOIN_INDEX,NO_GROUP_INDEX,NO_ORDER_INDEX, andNO_INDEX optimizer hints, which are equivalent to and intended to supersedeIGNORE INDEX index hints. Thus, you should expectUSE INDEX,FORCE INDEX, andIGNORE INDEX to be deprecated in a future release of MySQL, and at some time thereafter to be removed altogether.

These index-level optimizer hints are supported with both single-table and multi-tableDELETE statements.

For more information, seeIndex-Level Optimizer Hints.

Each hint requires index names, not column names. To refer to a primary key, use the namePRIMARY. To see the index names for a table, use theSHOW INDEX statement or the Information SchemaSTATISTICS table.

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

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 omitindex_list forUSE INDEX, which means“use no indexes.” Omittingindex_list forFORCE INDEX orIGNORE INDEX is a syntax error.
You can specify the scope of an index hint by adding aFOR clause to the hint. This provides more fine-grained control over optimizer 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, useFOR JOIN. To influence index usage for sorting or grouping rows, useFOR ORDER BY orFOR GROUP BY.
You can specify multiple index hints:
```
SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX FOR ORDER BY (i2) ORDER BY a;
```
It is not an 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 mixUSE INDEX andFORCE INDEX for the same table:
```
SELECT * FROM t1 USE INDEX FOR JOIN (i1) FORCE INDEX FOR JOIN (i2);
```

If an index hint includes noFOR clause, the scope of the hint is to apply 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)

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:

{USE|FORCE} INDEX is applied if present. (If not, the optimizer-determined set of indexes is used.)
IGNORE INDEX is applied over the result of the previous step. For example, the following two queries are equivalent:
```
SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX (i2) USE INDEX (i2);SELECT * FROM t1 USE INDEX (i1);
```

ForFULLTEXT searches, 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 withFOR ORDER BY orFOR GROUP BY are silently ignored. Index hints withFOR JOIN or noFOR modifier are honored. In contrast to how hints apply for non-FULLTEXT searches, the hint is used for all phases of query execution (finding rows and retrieval, grouping, and ordering). This is true even if the hint is given for a non-FULLTEXT index.
For example, the following two queries are equivalent:
```
SELECT * FROM t  USE INDEX (index1)  IGNORE INDEX FOR ORDER BY (index1)  IGNORE INDEX FOR GROUP BY (index1)  WHERE ... IN BOOLEAN MODE ... ;SELECT * FROM t  USE INDEX (index1)  WHERE ... IN BOOLEAN MODE ... ;
```

Index hints work withDELETE statements, but only if you use multi-tableDELETE syntax, as shown here:

mysql> EXPLAIN DELETE FROM t1 USE INDEX(col2)     -> WHERE col1 BETWEEN 1 AND 100 AND COL2 BETWEEN 1 AND 100\GERROR 1064 (42000): You have an error in your SQL syntax; check the manual thatcorresponds to your MySQL server version for the right syntax to use near 'useindex(col2) where col1 between 1 and 100 and col2 between 1 and 100' at line 1  mysql> EXPLAIN DELETE t1.* FROM t1 USE INDEX(col2)     -> WHERE col1 BETWEEN 1 AND 100 AND COL2 BETWEEN 1 AND 100\G*************************** 1. row ***************************           id: 1  select_type: DELETE        table: t1   partitions: NULL         type: rangepossible_keys: col2          key: col2      key_len: 5          ref: NULL         rows: 72     filtered: 11.11        Extra: Using where1 row in set, 1 warning (0.00 sec)

PREV HOME UP NEXT