MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
Index hints give the optimizer information about how to choose
indexes during query processing. Index hints apply only to
SELECT
and
UPDATE
statements.
Index hints are specified following a table name. (For the
general syntax for specifying tables in a
SELECT
statement, see
Section 13.2.9.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
] ...
The USE INDEX
(
hint tells
MySQL to use only one of the named indexes to find rows in the
table. The alternative syntax index_list
)IGNORE INDEX
(
tells MySQL to
not use some particular index or indexes. These hints are useful
if index_list
)EXPLAIN
shows that MySQL is
using the wrong index from the list of possible indexes.
The FORCE INDEX
hint acts like USE
INDEX (
, 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.
index_list
)
Each hint requires index names, not column names. To refer to a
primary key, use the name PRIMARY
. To see the
index names for a table, use the SHOW
INDEX
statement or the
INFORMATION_SCHEMA.STATISTICS
table.
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.
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 omit
index_list
for USE
INDEX
, which means “use no indexes.”
Omitting index_list
for
FORCE INDEX
or IGNORE
INDEX
is a syntax error.
You can specify the scope of an index hint by adding a
FOR
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, use FOR
JOIN
. To influence index usage for sorting or
grouping rows, use FOR ORDER BY
or
FOR 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 mix USE INDEX
and FORCE INDEX
for the same table:
SELECT * FROM t1 USE INDEX FOR JOIN (i1) FORCE INDEX FOR JOIN (i2);
If an index hint includes no FOR
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)
In MySQL 5.0, hint scope with no FOR
clause
was to apply only to row retrieval. To cause the server to use
this older behavior when no FOR
clause is
present, 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 source and replicas
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:
{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);
For FULLTEXT
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 with FOR
ORDER BY
or FOR GROUP BY
are
silently ignored. Index hints with FOR
JOIN
or no FOR
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 (index1) FOR ORDER BY IGNORE INDEX (index1) FOR GROUP BY WHERE ... IN BOOLEAN MODE ... ; SELECT * FROM t USE INDEX (index1) WHERE ... IN BOOLEAN MODE ... ;