The optimizer_switch
system
variable enables control over optimizer behavior. Its value 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
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 |
|
Set the named optimization to its default value |
|
Disable the named optimization |
|
Enable the named optimization |
The order of the commands in the value does not matter, although
the default
command is executed first if
present. Setting an opt_name
flag to
default
sets it to whichever of
on
or off
is its default
value. Specifying any given opt_name
more than once in the value is not permitted and causes an
error. Any errors in the value cause the assignment to fail with
an error, leaving the value of
optimizer_switch
unchanged.
The following list describes the permissible
opt_name
flag names, grouped by
optimization strategy:
Engine Condition Pushdown Flags
engine_condition_pushdown
(defaulton
)Controls engine condition pushdown.
For more information, see Section 8.2.1.4, “Engine Condition Pushdown Optimization”.
Index Merge Flags
index_merge
(defaulton
)Controls all Index Merge optimizations.
index_merge_intersection
(defaulton
)Controls the Index Merge Intersection Access optimization.
index_merge_sort_union
(defaulton
)Controls the Index Merge Sort-Union Access optimization.
index_merge_union
(defaulton
)Controls the Index Merge Union Access optimization.
For more information, see Section 8.2.1.3, “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,
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