WHERE b = 1 AND c = 2 AND d = 3; 

The hint takes precedence over optimizer_switch . Index Merge is used for (i_b, i_c)  in this case. 

SET optimizer_switch=’index_merge_intersection=on’; SELECT /*+ INDEX_MERGE(t1 i_b) */ * FROM t1 WHERE b = 1 AND c = 2 AND d = 3; 

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. 

SET optimizer_switch=’index_merge_intersection=off’; SELECT /*+ INDEX_MERGE(t1 i_b) */ * FROM t1 WHERE b = 1 AND c = 2 AND d = 3; 

The hint specifies only one index, so it is inapplicable, and the optimizer_switch  flag ( off ) applies. Index Merge is not used. 

•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. 

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. 

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: 

SELECT /*+ NO_ORDER_INDEX(t1 i_b,i_c) */ a,c FROM t1 ORDER BY a; 

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: 

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 

