8.8.5.2 Controlling Switchable Optimizations

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 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 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 table lists the permissible
opt_name flag names, grouped by
optimization strategy.

Optimization

Flag Name

Meaning

Default

Engine Condition Pushdown

engine_condition_pushdown

Controls engine condition pushdown

ON

Index Merge

index_merge

Controls all Index Merge optimizations

ON

index_merge_intersection

Controls the Index Merge Intersection Access optimization

ON

index_merge_sort_union

Controls the Index Merge Sort-Union Access optimization

ON

index_merge_union

Controls the Index Merge Union Access optimization

ON

The flag for engine condition pushdown was added in MySQL
5.5.3.

For more information about individual optimization strategies,
see the following sections:

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:

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: