Optimizer Hints WHERE b = 1 AND c = 2 AND d = 3; SET optimizer_switch=’index_merge_intersection=on’; SELECT /*+ INDEX_MERGE(t1 i_b) */ * FROM t1 WHERE b = 1 AND c = 2 AND d = 3; SET optimizer_switch=’index_merge_intersection=off’; SELECT /*+ INDEX_MERGE(t1 i_b) */ * FROM t1 WHERE b = 1 AND c = 2 AND d = 3; mysql>  EXPLAIN SELECT /*+ NO_ORDER_INDEX(t1) */ a,c FROM t1 -> USE INDEX FOR ORDER BY (i_a) ORDER BY a\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 256 filtered: 100.00 Extra: Using filesort SELECT /*+ NO_ORDER_INDEX(t1 i_b,i_c) */ a,c FROM t1 ORDER BY a; 1801 Attempting to combine  NO_ORDER_INDEX  for the table as a whole with  USE INDEX FOR ORDER BY does not work to do this, because  NO_ORDER_BY  causes  USE INDEX  to be ignored, as shown here: To replicate the effect that  USE INDEX  has in the query  SELECT a,c FROM t1 USE INDEX FOR ORDER BY (i_a) ORDER BY a , you can use the  NO_ORDER_INDEX  optimizer hint to cover all indexes on the table except the one that is desired like this: The index-level optimizer hints  GROUP_INDEX ,  NO_GROUP_INDEX ,  INDEX , NO_INDEX , JOIN_INDEX , NO_JOIN_INDEX ,  ORDER_INDEX , and  NO_ORDER_INDEX  hints all take precedence over all other optimizer hints, including other index-level optimizer hints. Any other optimizer hints are applied only to the indexes permitted by these. The  GROUP_INDEX ,  INDEX ,  JOIN_INDEX , and  ORDER_INDEX  hints are all equivalent to  FORCE INDEX and not to  USE INDEX . This is because using one or more of these hints means that a table scan is used only if there is no way to use one of the named indexes to find rows in the table. To cause MySQL to use the same index or set of indexes as with a given instance of  USE INDEX , you can use  NO_INDEX , NO_JOIN_INDEX ,  NO_GROUP_INDEX ,  NO_ORDER_INDEX , or some combination of these. The hint specifies only one index, so it is inapplicable, and the  optimizer_switch  flag ( off ) applies. Index Merge is not used. The hint specifies only one index, so it is inapplicable, and the  optimizer_switch  flag ( on ) applies. Index Merge is used if the optimizer assesses it to be cost efficient. The hint takes precedence over  optimizer_switch . Index Merge is used for  (i_b, i_c)  in this case. •The index-level optimizer hints  GROUP_INDEX ,  INDEX ,  JOIN_INDEX , and  ORDER_INDEX  all take precedence over the equivalent  FORCE INDEX  hints; that is, they cause the  FORCE INDEX  hints to be ignored. Likewise, the  NO_GROUP_INDEX ,  NO_INDEX ,  NO_JOIN_INDEX , and  NO_ORDER_INDEX  hints all take precedence over any  IGNORE INDEX  equivalents, also causing them to be ignored.