Skip navigation links
MySQL 5.4 Reference Manual :: 7 Optimization :: 7.2 Optimizing SELECT and Other Statements :: 7.2.19 Using optimizer_switch to Control the Optimizer
7.2.20 How to Avoid Table Scans »
Section Navigation      [Toggle]

7.2.19. Using optimizer_switch to Control the Optimizer

The optimizer_switch system variable enables control over optimizer behavior.

As of MySQL 5.4.2, the value of the optimizer_switch system 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 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

To change the value of optimizer_switch, assign a value consisting of a comma-separated list of one or more commands: 

SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';

Each command value should have one of the forms shown in the following table.

Command Syntax Meaning
default Reset every optimization to its default value
opt_name=default Set the named optimization to its default value
opt_name=off Disable the named optimization
opt_name=on Enable the named optimization

The order of the commands in the value does not matter, although default is executed first if present. Specifying any given opt_name more than once in the value is not allowed and causes an error. Any errors in the value cause the entire statement to fail with an error and the current value of optimizer_switch remains unchanged.

The following table lists the allowable opt_name flag names.

Flag Name Meaning
index_merge Controls all Index Merge optimizations
index_merge_union Controls the Index Merge Union Access optimization
index_merge_sort_union Controls the Index Merge Sort-Union Access optimization
index_merge_intersection Controls the Index Merge Intersection Access optimization

For information about Index Merge, see Section 7.2.6, “Index Merge Optimization”.

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
1 row in set (0.00 sec)

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';
Query OK, 0 rows affected (0.00 sec)

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
1 row in set (0.00 sec)