optimizer_switch

Command-Line Format	--optimizer_switch=value
Option-File Format	optimizer_switch
System Variable Name	optimizer_switch
Variable Scope	Global, Session
Dynamic Variable	Yes
	Permitted Values (<= 5.5.2)
	Type	set
	Valid Values	index_merge={on|off}
		index_merge_intersection={on|off}
		index_merge_sort_union={on|off}
		index_merge_union={on|off}
	Permitted Values (>= 5.5.3)
	Type	set
	Valid Values	engine_condition_pushdown={on|off}
		index_merge={on|off}
		index_merge_intersection={on|off}
		index_merge_sort_union={on|off}
		index_merge_union={on|off}

The optimizer_switch system variable enables control over optimizer behavior. The value of this variable is a set of flags, each of which has a value of on or off to indicate whether the corresponding optimizer behavior is enabled or disabled. This variable has global and session values and can be changed at runtime. The global default can be set at server startup.

To see the current set of optimizer flags, select the variable value:

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

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:

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

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 will perform better without them, set the variable value like this:

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

参考：http://dev.mysql.com/doc/refman/5.5/en/switchable-optimizations.html#