Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.3Mb
PDF (A4) - 35.3Mb
PDF (RPM) - 34.3Mb
EPUB - 8.6Mb
HTML Download (TGZ) - 8.4Mb
HTML Download (Zip) - 8.4Mb
HTML Download (RPM) - 7.2Mb
Eclipse Doc Plugin (TGZ) - 9.2Mb
Eclipse Doc Plugin (Zip) - 11.4Mb
Man Pages (TGZ) - 200.3Kb
Man Pages (Zip) - 305.5Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Controlling Switchable Optimizations

9.9.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 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 SyntaxMeaning
defaultReset every optimization to its default value
opt_name=defaultSet the named optimization to its default value
opt_name=offDisable the named optimization
opt_name=onEnable 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.

OptimizationFlag NameMeaningDefault
Batched Key Accessbatched_key_accessControls use of BKA join algorithmOFF
Block Nested-Loopblock_nested_loopControls use of BNL join algorithmON
Condition Filteringcondition_fanout_filterControls use of condition filteringON
Engine Condition Pushdownengine_condition_pushdownControls engine condition pushdownON
Index Condition Pushdownindex_condition_pushdownControls index condition pushdownON
Index Extensionsuse_index_extensionsControls use of index extensionsON
Index Mergeindex_mergeControls all Index Merge optimizationsON
 index_merge_intersectionControls the Index Merge Intersection Access optimizationON
 index_merge_sort_unionControls the Index Merge Sort-Union Access optimizationON
 index_merge_unionControls the Index Merge Union Access optimizationON
Multi-Range ReadmrrControls the Multi-Range Read strategyON
 mrr_cost_basedControls use of cost-based MRR if mrr=onON
Semi-joinsemijoinControls all semi-join strategiesON
 firstmatchControls the semi-join FirstMatch strategyON
 loosescanControls the semi-join LooseScan strategy (not to be confused with LooseScan for GROUP BY)ON
 duplicateweedoutControls the semi-join Duplicate Weedout strategyON
Subquery materializationmaterializationControls materialization (including semi-join materialization)ON
 subquery_materialization_cost_basedUsed cost-based materialization choiceON
Derived table mergingderived_mergeControls merging of derived tables and views into outer query blockON

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.

The semijoin, firstmatch, loosescan, duplicateweedout (added in MySQL 5.7.8), and materialization flags enable control over semi-join and subquery materialization strategies. The semijoin flag controls whether semi-joins are used. If it is set to on, the firstmatch and loosescan flags enable finer control over the permitted semi-join strategies. The materialization flag controls whether subquery materialization is used. If semijoin and materialization are both on, semi-joins also use materialization where applicable. These flags are on by default.

If the duplicateweedout semi-join strategy is disabled, it is not used unless all other applicable strategies are also disabled.

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 -> EXISTS subquery transformation.

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 9.2.1.18.3, “Optimizing Derived Tables and View References”.

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:

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

User Comments
Sign Up Login You must be logged in to post a comment.