Index Hints •You can specify multiple index hints: It is not an error to name the same index in several hints (even within the same hint): However, it is an error to mix  USE INDEX  and  FORCE INDEX  for the same table: 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: is equivalent to this combination of hints: 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: The index hints then are applied for each scope in the following order: is equivalent to: For  FULLTEXT  searches, index hints work as follows: For example, the following two queries are equivalent: •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. 1808 2. IGNORE INDEX  is applied over the result of the previous step. For example, the following two queries are equivalent: 1. {USE|FORCE} INDEX  is applied if present. (If not, the optimizer-determined set of indexes is used.) SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX FOR ORDER BY (i2) ORDER BY a; SELECT * FROM t1 USE INDEX (i1) USE INDEX (i1,i1); SELECT * FROM t1 USE INDEX FOR JOIN (i1) FORCE INDEX FOR JOIN (i2); IGNORE INDEX (i1) IGNORE INDEX FOR JOIN (i1) IGNORE INDEX FOR ORDER BY (i1) IGNORE INDEX FOR GROUP BY (i1) SELECT * FROM t1 USE INDEX () IGNORE INDEX (i2) USE INDEX (i1) USE INDEX (i2); SELECT * FROM t1 USE INDEX (i1,i2) IGNORE INDEX (i2); SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX (i2) USE INDEX (i2); SELECT * FROM t1 USE INDEX (i1);