content_image/853111.jpg
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 ( 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. 

<img src='content_image/853111.jpg'>

## Note 

As of MySQL 8.0.20, the server supports the index-level optimizer hints JOIN_INDEX , GROUP_INDEX , ORDER_INDEX , and INDEX , which are equivalent to and intended to supersede FORCE INDEX  index hints, as well as the NO_JOIN_INDEX , NO_GROUP_INDEX , NO_ORDER_INDEX , and NO_INDEX optimizer hints, which are equivalent to and intended to supersede IGNORE INDEX index hints. Thus, you should expect USE INDEX , FORCE INDEX , and IGNORE INDEX  to be deprecated in a future release of MySQL, and at some time thereafter to be removed altogether. For more information, see Index-Level Optimizer Hints. 

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 . 

