mysql>  SELECT @@optimizer_switch\G *************************** 1. row *************************** @@optimizer_switch: index_merge=on,index_merge_union=on, index_merge_sort_union=on,index_merge_intersection=on, engine_condition_pushdown=on,index_condition_pushdown=on, mrr=on,mrr_cost_based=on,block_nested_loop=on, batched_key_access=off,materialization=on,semijoin=on, loosescan=on, firstmatch=on, subquery_materialization_cost_based=on, use_index_extensions=on,condition_fanout_filter=on, derived_merge=on,use_invisible_indexes=off,skip_scan=on, hash_join=on,subquery_to_derived=off, prefer_ordering_index=on mysql>  SET optimizer_switch=’index_merge_union=off,index_merge_sort_union=off’; mysql>  SELECT @@optimizer_switch\G *************************** 1. row *************************** @@optimizer_switch: index_merge=on,index_merge_union=off, index_merge_sort_union=off,index_merge_intersection=on, engine_condition_pushdown=on,index_condition_pushdown=on, mrr=on,mrr_cost_based=on,block_nested_loop=on, batched_key_access=off,materialization=on,semijoin=on, loosescan=on, firstmatch=on, subquery_materialization_cost_based=on, use_index_extensions=on,condition_fanout_filter=on, derived_merge=on,use_invisible_indexes=off,skip_scan=on, hash_join=on,subquery_to_derived=off, prefer_ordering_index=on SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1 FROM t3 WHERE f1 > 30 AND f1 < 33; SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...; SELECT /*+ NO_ICP(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...; SELECT /*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */ * FROM t1 ...; EXPLAIN SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE ...; SELECT /*+ MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt; INSERT /*+ SET_VAR(foreign_key_checks=OFF) */ INTO t2 VALUES(2); Another way to control the optimizer is by using optimizer hints, which can be specified within individual statements. Because optimizer hints apply on a per-statement basis, they provide finer control over statement execution plans than can be achieved using  optimizer_switch . For example, you can enable an optimization for one table in a statement and disable the optimization for a different table. Hints within a statement take precedence over  optimizer_switch  flags. Examples: One means of control over optimizer strategies is to set the  optimizer_switch  system variable (see Section 8.9.2, “Switchable Optimizations” ). Changes to this variable affect execution of all subsequent queries; to affect one query differently from another, it is necessary to change  optimizer_switch  before each one. If the server is using the Index Merge Union or Index Merge Sort-Union access methods for certain queries and you want to check whether the optimizer can perform better without them, set the variable value like this: When you assign a value to  optimizer_switch , flags that are not mentioned keep their current values. This makes it possible to enable or disable specific optimizer behaviors in a single statement without affecting other behaviors. The statement does not depend on what other optimizer flags exist and what their values are. Suppose that all Index Merge optimizations are enabled: Optimizer Hints 8.9.3 Optimizer Hints 1791