8.9.2 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 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
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
(defaultoff
)Controls use of BKA join algorithm.
For
batched_key_access
to have any effect when set toon
, themrr
flag must also beon
. Currently, the cost estimation for MRR is too pessimistic. Hence, it is also necessary formrr_cost_based
to beoff
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
(defaulton
)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
(defaulton
)Controls use of condition filtering.
For more information, see Section 8.2.1.12, “Condition Filtering”.
Derived Table Merging Flags
derived_merge
(defaulton
)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, anALGORITHM
directive for a view takes precedence over thederived_merge
setting. By default, the flag ison
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
(defaulton
)Controls engine condition pushdown.
For more information, see Section 8.2.1.4, “Engine Condition Pushdown Optimization”.
Index Condition Pushdown Flags
index_condition_pushdown
(defaulton
)Controls index condition pushdown.
For more information, see Section 8.2.1.5, “Index Condition Pushdown Optimization”.
Index Extensions Flags
use_index_extensions
(defaulton
)Controls use of index extensions.
For more information, see Section 8.3.9, “Use of Index Extensions”.
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”.
Multi-Range Read Flags
mrr
(defaulton
)Controls the Multi-Range Read strategy.
mrr_cost_based
(defaulton
)Controls use of cost-based MRR if
mrr=on
.
For more information, see Section 8.2.1.10, “Multi-Range Read Optimization”.
Semijoin Flags
semijoin
(defaulton
)Controls all semijoin strategies.
duplicateweedout
(defaulton
)Controls the semijoin Duplicate Weedout strategy.
firstmatch
(defaulton
)Controls the semijoin FirstMatch strategy.
loosescan
(defaulton
)Controls the semijoin LooseScan strategy (not to be confused with Loose Index Scan for
GROUP BY
).
The
semijoin
,firstmatch
,loosescan
, andduplicateweedout
flags enable control over semijoin strategies. Thesemijoin
flag controls whether semijoins are used. If it is set toon
, thefirstmatch
andloosescan
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
andmaterialization
are bothon
, semijoins also use materialization where applicable. These flags areon
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
(defaulton
)Controls materialization (including semijoin materialization).
subquery_materialization_cost_based
(defaulton
)Use cost-based materialization choice.
The
materialization
flag controls whether subquery materialization is used. Ifsemijoin
andmaterialization
are bothon
, semijoins also use materialization where applicable. These flags areon
by default.The
subquery_materialization_cost_based
flag enables control over the choice between subquery materialization andIN
-to-EXISTS
subquery transformation. If the flag ison
(the default), the optimizer performs a cost-based choice between subquery materialization andIN
-to-EXISTS
subquery transformation if either method could be used. If the flag isoff
, the optimizer chooses subquery materialization overIN
-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,
subquery_materialization_cost_based=on,
use_index_extensions=on,
condition_fanout_filter=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,
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,
subquery_materialization_cost_based=on,
use_index_extensions=on,
condition_fanout_filter=on