MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6
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,
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,duplicateweedout=on,
subquery_materialization_cost_based=on,
use_index_extensions=on,
condition_fanout_filter=on,derived_merge=on,
prefer_ordering_index=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:
Batched Key Access Flags
batched_key_access
(default off
)
Controls use of BKA join algorithm.
For batched_key_access
to
have any effect when set to on
, the
mrr
flag must also be
on
. Currently, the cost estimation for
MRR is too pessimistic. Hence, it is also necessary for
mrr_cost_based
to be
off
for BKA to be used.
For more information, see Section 8.2.1.11, “Block Nested-Loop and Batched Key Access Joins”.
Block Nested-Loop Flags
block_nested_loop
(default on
)
Controls use of BNL join algorithm.
For more information, see Section 8.2.1.11, “Block Nested-Loop and Batched Key Access Joins”.
Condition Filtering Flags
condition_fanout_filter
(default on
)
Controls use of condition filtering.
For more information, see Section 8.2.1.12, “Condition Filtering”.
Derived Table Merging Flags
derived_merge
(default
on
)
Controls merging of derived tables and views into outer query block.
The derived_merge
flag
controls whether the optimizer attempts to merge derived
tables and view references into the outer query block,
assuming that no other rule prevents merging; for example,
an ALGORITHM
directive for a view takes
precedence over the
derived_merge
setting. By
default, the flag is on
to enable
merging.
For more information, see Section 8.2.2.4, “Optimizing Derived Tables and View References with Merging or Materialization”.
Engine Condition Pushdown Flags
engine_condition_pushdown
(default on
)
Controls engine condition pushdown.
For more information, see Section 8.2.1.4, “Engine Condition Pushdown Optimization”.
Index Condition Pushdown Flags
index_condition_pushdown
(default on
)
Controls index condition pushdown.
For more information, see Section 8.2.1.5, “Index Condition Pushdown Optimization”.
Index Extensions Flags
use_index_extensions
(default on
)
Controls use of index extensions.
For more information, see Section 8.3.9, “Use of Index Extensions”.
Index Merge Flags
index_merge
(default
on
)
Controls all Index Merge optimizations.
index_merge_intersection
(default on
)
Controls the Index Merge Intersection Access optimization.
index_merge_sort_union
(default on
)
Controls the Index Merge Sort-Union Access optimization.
index_merge_union
(default on
)
Controls the Index Merge Union Access optimization.
For more information, see Section 8.2.1.3, “Index Merge Optimization”.
Limit Optimization Flags
prefer_ordering_index
(default on
)
Controls whether, in the case of a query having an
ORDER BY
or GROUP
BY
with a LIMIT
clause, the
optimizer tries to use an ordered index instead of an
unordered index, a filesort, or some other optimization.
This optimzation is performed by default whenever the
optimizer determines that using it would allow for
faster execution of the query.
Because the algorithm that makes this determination
cannot handle every conceivable case (due in part to the
assumption that the distribution of data is always more
or less uniform), there are cases in which this
optimization may not be desirable. Prior to MySQL
5.7.33, it ws not possible to disable this optimization,
but in MySQL 5.7.33 and later, while it remains the
default behavior, it can be disabled by setting the
prefer_ordering_index
flag to off
.
For more information and examples, see Section 8.2.1.17, “LIMIT Query Optimization”.
Multi-Range Read Flags
mrr
(default
on
)
Controls the Multi-Range Read strategy.
mrr_cost_based
(default on
)
Controls use of cost-based MRR if
mrr=on
.
For more information, see Section 8.2.1.10, “Multi-Range Read Optimization”.
Semijoin Flags
duplicateweedout
(default on
)
Controls the semijoin Duplicate Weedout strategy.
firstmatch
(default
on
)
Controls the semijoin FirstMatch strategy.
loosescan
(default
on
)
Controls the semijoin LooseScan strategy (not to be
confused with Loose Index Scan for GROUP
BY
).
semijoin
(default
on
)
Controls all semijoin strategies.
The semijoin
,
firstmatch
,
loosescan
, and
duplicateweedout
flags
enable control over semijoin strategies. The
semijoin
flag controls
whether semijoins are used. If it is set to
on
, the
firstmatch
and
loosescan
flags enable
finer control over the permitted semijoin strategies.
If the duplicateweedout
semijoin strategy is disabled, it is not used unless all
other applicable strategies are also disabled.
If semijoin
and
materialization
are both
on
, semijoins also use materialization
where applicable. These flags are on
by
default.
For more information, see Section 8.2.2.1, “Optimizing Subqueries, Derived Tables, and View References with Semijoin Transformations”.
Subquery Materialization Flags
materialization
(default on
)
Controls materialization (including semijoin materialization).
subquery_materialization_cost_based
(default on
)
Use cost-based materialization choice.
The materialization
flag
controls whether subquery materialization is used. If
semijoin
and
materialization
are both
on
, semijoins also use materialization
where applicable. These flags are on
by
default.
The
subquery_materialization_cost_based
flag enables control over the choice between subquery
materialization and
IN
-to-EXISTS
subquery
transformation. If the flag is on
(the
default), the optimizer performs a cost-based choice between
subquery materialization and
IN
-to-EXISTS
subquery
transformation if either method could be used. If the flag
is off
, the optimizer chooses subquery
materialization over
IN
-to-EXISTS
subquery
transformation.
For more information, see Section 8.2.2, “Optimizing Subqueries, Derived Tables, and View References”.
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,
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,duplicateweedout=on,
subquery_materialization_cost_based=on,
use_index_extensions=on,
condition_fanout_filter=on,derived_merge=on,
prefer_ordering_index=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 performs 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, 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,duplicateweedout=on, subquery_materialization_cost_based=on, use_index_extensions=on, condition_fanout_filter=on,derived_merge=on, prefer_ordering_index=on