As of MySQL 3.23.12, you can provide hints to give the optimizer
information about how to choose indexes during query processing.
Section 12.2.7.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
)]index_hint
: USE {INDEX|KEY} (index_list
)] | IGNORE {INDEX|KEY} (index_list
)] | FORCE {INDEX|KEY} (index_list
)]index_list
:index_name
[,index_name
] ...
By specifying USE INDEX
(
, you can tell
MySQL to use only one of the named indexes to find rows in the
table. The alternative syntax index_list
)IGNORE INDEX
(
can be used to
tell 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.
From MySQL 4.0.9 on, you can also use FORCE
INDEX
, which acts like USE INDEX
(
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.
index_list
)
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 ambiguous, an error occurs.
Index hints do not work for FULLTEXT
indexes.
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.
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;
Index hints are accepted but ignored for
UPDATE
statements.
User Comments
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;
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.