MySQL :: MySQL 8.0 Reference Manual :: 10.9.2 Switchable Optimizations (original) (raw)

10.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,use_invisible_indexes=off,skip_scan=on,
                    hash_join=on,subquery_to_derived=off,
                    prefer_ordering_index=on,hypergraph_optimizer=off,
                    derived_condition_pushdown=on
1 row in set (0.00 sec)

To change the value ofoptimizer_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 optname flag todefault sets it to whichever ofon or off is its default value. Specifying any given optname 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 ofoptimizer_switch unchanged.

The following list describes the permissible_optname_ 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, 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, seeSection 10.2.1.12, “Block Nested-Loop and Batched Key Access Joins”.
Block Nested-Loop Flags
- block_nested_loop (default on)
  Controls use of BNL join algorithm. In MySQL 8.0.18 and later, this also controls use of hash joins, as do theBNL andNO_BNL optimizer hints. In MySQL 8.0.20 and later, block nested loop support is removed from the MySQL server, and this flag controls the use of hash joins only, as do the referenced optimizer hints.
  For more information, seeSection 10.2.1.12, “Block Nested-Loop and Batched Key Access Joins”.
Condition Filtering Flags
- condition_fanout_filter (default on)
  Controls use of condition filtering.
  For more information, seeSection 10.2.1.13, “Condition Filtering”.
Derived Condition Pushdown Flags
- derived_condition_pushdown (default on)
  Controls derived condition pushdown.
  For more information, seeSection 10.2.2.5, “Derived Condition Pushdown Optimization”
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, view references, and common table expressions into the outer query block, assuming that no other rule prevents merging; for example, an ALGORITHM directive for a view takes precedence over thederived_merge setting. By default, the flag is on to enable merging.
  For more information, seeSection 10.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”.
Engine Condition Pushdown Flags
- engine_condition_pushdown (default on)
  Controls engine condition pushdown.
  For more information, seeSection 10.2.1.5, “Engine Condition Pushdown Optimization”.
Hash Join Flags
- hash_join (defaulton)
  Controls hash joins in MySQL 8.0.18 only, and has no effect in any subsequent version. In MySQL 8.0.19 and later, to control hash join usage, use theblock_nested_loop flag, instead.
  For more information, see Section 10.2.1.4, “Hash Join Optimization”.
Index Condition Pushdown Flags
- index_condition_pushdown (default on)
  Controls index condition pushdown.
  For more information, seeSection 10.2.1.6, “Index Condition Pushdown Optimization”.
Index Extensions Flags
- use_index_extensions (default on)
  Controls use of index extensions.
  For more information, seeSection 10.3.10, “Use of Index Extensions”.
Index Merge Flags
- index_merge (defaulton)
  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, seeSection 10.2.1.3, “Index Merge Optimization”.
Index Visibility Flags
- use_invisible_indexes (default off)
  Controls use of invisible indexes.
  For more information, seeSection 10.3.12, “Invisible Indexes”.
Limit Optimization Flags
- prefer_ordering_index (default on)
  Controls whether, in the case of a query having anORDER 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 optimization 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 8.0.21, it was not possible to disable this optimization, but in MySQL 8.0.21 and later, while it remains the default behavior, it can be disabled by setting theprefer_ordering_index flag to off.
  For more information and examples, seeSection 10.2.1.19, “LIMIT Query Optimization”.
Multi-Range Read Flags
- mrr (defaulton)
  Controls the Multi-Range Read strategy.
- mrr_cost_based (default on)
  Controls use of cost-based MRR ifmrr=on.
  For more information, seeSection 10.2.1.11, “Multi-Range Read Optimization”.
Semijoin Flags
- duplicateweedout (default on)
  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).
- semijoin (defaulton)
  Controls all semijoin strategies.
  In MySQL 8.0.17 and later, this also applies to the antijoin optimization.
  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 are on by default.
  For more information, see Section 10.2.2.1, “Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations”.
Skip Scan Flags
- skip_scan (defaulton)
  Controls use of Skip Scan access method.
  For more information, seeSkip Scan Range Access Method.
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. Ifsemijoin andmaterialization are bothon, semijoins also use materialization where applicable. These flags are on by default.
  Thesubquery_materialization_cost_based flag enables control over the choice between subquery materialization andIN-to-EXISTS subquery transformation. If the flag is on (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 is off, the optimizer chooses subquery materialization overIN-to-EXISTS subquery transformation.
  For more information, seeSection 10.2.2, “Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions”.

Subquery Transformation Flags

subquery_to_derived (default off)
Beginning with MySQL 8.0.21, the optimizer is able in many cases to transform a scalar subquery in aSELECT, WHERE,JOIN, or HAVING clause into a left outer join on a derived table. (Depending on the nullability of the derived table, this can sometimes be simplified further to an inner join.) This can be done for a subquery which meets the following conditions:
* The subquery does not make use of any nondeterministic functions, such asRAND().
* The subquery is not an ANY orALL subquery which can be rewritten to useMIN() orMAX().
* The parent query does not set a user variable, since rewriting it may affect the order of execution, which could lead to unexpected results if the variable is accessed more than once in the same query.
* The subquery should not be correlated, that is, it should not reference a column from a table in the outer query, or contain an aggregate that is evaluated in the outer query.

Prior to MySQL 8.0.22, the subquery could not contain aGROUP BY clause.
This optimization can also be applied to a table subquery which is the argument to IN,NOT IN, EXISTS, orNOT EXISTS, that does not contain aGROUP BY.
The default value for this flag isoff, since, in most cases, enabling this optimization does not produce any noticeable improvement in performance (and in many cases can even make queries run more slowly), but you can enable the optimization by setting thesubquery_to_derived flag to on. It is primarily intended for use in testing.
Example, using a scalar subquery:

d  
mysql> CREATE TABLE t1(a INT);  
mysql> CREATE TABLE t2(a INT);  
mysql> INSERT INTO t1 VALUES ROW(1), ROW(2), ROW(3), ROW(4);  
mysql> INSERT INTO t2 VALUES ROW(1), ROW(2);  
mysql> SELECT * FROM t1  
    ->     WHERE t1.a > (SELECT COUNT(a) FROM t2);  
+------+  
| a    |  
+------+  
|    3 |  
|    4 |  
+------+  
mysql> SELECT @@optimizer_switch LIKE '%subquery_to_derived=off%';  
+-----------------------------------------------------+  
| @@optimizer_switch LIKE '%subquery_to_derived=off%' |  
+-----------------------------------------------------+  
|                                                   1 |  
+-----------------------------------------------------+  
mysql> EXPLAIN SELECT * FROM t1 WHERE t1.a > (SELECT COUNT(a) FROM t2)\G  
*************************** 1. row ***************************  
           id: 1  
  select_type: PRIMARY  
        table: t1  
   partitions: NULL  
         type: ALL  
possible_keys: NULL  
          key: NULL  
      key_len: NULL  
          ref: NULL  
         rows: 4  
     filtered: 33.33  
        Extra: Using where  
*************************** 2. row ***************************  
           id: 2  
  select_type: SUBQUERY  
        table: t2  
   partitions: NULL  
         type: ALL  
possible_keys: NULL  
          key: NULL  
      key_len: NULL  
          ref: NULL  
         rows: 2  
     filtered: 100.00  
        Extra: NULL  
mysql> SET @@optimizer_switch='subquery_to_derived=on';  
mysql> SELECT @@optimizer_switch LIKE '%subquery_to_derived=off%';  
+-----------------------------------------------------+  
| @@optimizer_switch LIKE '%subquery_to_derived=off%' |  
+-----------------------------------------------------+  
|                                                   0 |  
+-----------------------------------------------------+  
mysql> SELECT @@optimizer_switch LIKE '%subquery_to_derived=on%';  
+----------------------------------------------------+  
| @@optimizer_switch LIKE '%subquery_to_derived=on%' |  
+----------------------------------------------------+  
|                                                  1 |  
+----------------------------------------------------+  
mysql> EXPLAIN SELECT * FROM t1 WHERE t1.a > (SELECT COUNT(a) FROM t2)\G  
*************************** 1. row ***************************  
           id: 1  
  select_type: PRIMARY  
        table: <derived2>  
   partitions: NULL  
         type: ALL  
possible_keys: NULL  
          key: NULL  
      key_len: NULL  
          ref: NULL  
         rows: 1  
     filtered: 100.00  
        Extra: NULL  
*************************** 2. row ***************************  
           id: 1  
  select_type: PRIMARY  
        table: t1  
   partitions: NULL  
         type: ALL  
possible_keys: NULL  
          key: NULL  
      key_len: NULL  
          ref: NULL  
         rows: 4  
     filtered: 33.33  
        Extra: Using where; Using join buffer (hash join)  
*************************** 3. row ***************************  
           id: 2  
  select_type: DERIVED  
        table: t2  
   partitions: NULL  
         type: ALL  
possible_keys: NULL  
          key: NULL  
      key_len: NULL  
          ref: NULL  
         rows: 2  
     filtered: 100.00  
        Extra: NULL

As can be seen from executing SHOW WARNINGS immediately following the secondEXPLAIN statement, with the optimization enabled, the query SELECT * FROM t1 WHERE t1.a > (SELECT COUNT(a) FROM t2) is rewritten in a form similar to what is shown here:

SELECT t1.a FROM t1  
    JOIN  ( SELECT COUNT(t2.a) AS c FROM t2 ) AS d  
            WHERE t1.a > d.c;

Example, using a query with IN (_`subquery`_):

mysql> DROP TABLE IF EXISTS t1, t2;  
mysql> CREATE TABLE t1 (a INT, b INT);  
mysql> CREATE TABLE t2 (a INT, b INT);  
mysql> INSERT INTO t1 VALUES ROW(1,10), ROW(2,20), ROW(3,30);  
mysql> INSERT INTO t2  
    ->    VALUES ROW(1,10), ROW(2,20), ROW(3,30), ROW(1,110), ROW(2,120), ROW(3,130);  
mysql> SELECT * FROM t1  
    ->     WHERE   t1.b < 0  
    ->             OR  
    ->             t1.a IN (SELECT t2.a + 1 FROM t2);  
+------+------+  
| a    | b    |  
+------+------+  
|    2 |   20 |  
|    3 |   30 |  
+------+------+  
mysql> SET @@optimizer_switch="subquery_to_derived=off";  
mysql> EXPLAIN SELECT * FROM t1  
    ->             WHERE   t1.b < 0  
    ->                     OR  
    ->                     t1.a IN (SELECT t2.a + 1 FROM t2)\G  
*************************** 1. row ***************************  
           id: 1  
  select_type: PRIMARY  
        table: t1  
   partitions: NULL  
         type: ALL  
possible_keys: NULL  
          key: NULL  
      key_len: NULL  
          ref: NULL  
         rows: 3  
     filtered: 100.00  
        Extra: Using where  
*************************** 2. row ***************************  
           id: 2  
  select_type: DEPENDENT SUBQUERY  
        table: t2  
   partitions: NULL  
         type: ALL  
possible_keys: NULL  
          key: NULL  
      key_len: NULL  
          ref: NULL  
         rows: 6  
     filtered: 100.00  
        Extra: Using where  
mysql> SET @@optimizer_switch="subquery_to_derived=on";  
mysql> EXPLAIN SELECT * FROM t1  
    ->             WHERE   t1.b < 0  
    ->                     OR  
    ->                     t1.a IN (SELECT t2.a + 1 FROM t2)\G  
*************************** 1. row ***************************  
           id: 1  
  select_type: PRIMARY  
        table: t1  
   partitions: NULL  
         type: ALL  
possible_keys: NULL  
          key: NULL  
      key_len: NULL  
          ref: NULL  
         rows: 3  
     filtered: 100.00  
        Extra: NULL  
*************************** 2. row ***************************  
           id: 1  
  select_type: PRIMARY  
        table: <derived2>  
   partitions: NULL  
         type: ref  
possible_keys: <auto_key0>  
          key: <auto_key0>  
      key_len: 9  
          ref: std2.t1.a  
         rows: 2  
     filtered: 100.00  
        Extra: Using where; Using index  
*************************** 3. row ***************************  
           id: 2  
  select_type: DERIVED  
        table: t2  
   partitions: NULL  
         type: ALL  
possible_keys: NULL  
          key: NULL  
      key_len: NULL  
          ref: NULL  
         rows: 6  
     filtered: 100.00  
        Extra: Using temporary

Checking and simplifying the result ofSHOW WARNINGS after executing EXPLAIN on this query shows that, when thesubquery_to_derived flag enabled, SELECT * FROM t1 WHERE t1.b < 0 OR t1.a IN (SELECT t2.a + 1 FROM t2) is rewritten in a form similar to what is shown here:

SELECT a, b FROM t1  
    LEFT JOIN (SELECT DISTINCT a + 1 AS e FROM t2) d  
    ON t1.a = d.e  
    WHERE   t1.b < 0  
            OR  
            d.e IS NOT NULL;

Example, using a query with EXISTS (_`subquery`_) and the same tables and data as in the previous example:

mysql> SELECT * FROM t1  
    ->     WHERE   t1.b < 0  
    ->             OR  
    ->             EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1);  
+------+------+  
| a    | b    |  
+------+------+  
|    1 |   10 |  
|    2 |   20 |  
+------+------+  
mysql> SET @@optimizer_switch="subquery_to_derived=off";  
mysql> EXPLAIN SELECT * FROM t1  
    ->             WHERE   t1.b < 0  
    ->                     OR  
    ->                     EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1)\G  
*************************** 1. row ***************************  
           id: 1  
  select_type: PRIMARY  
        table: t1  
   partitions: NULL  
         type: ALL  
possible_keys: NULL  
          key: NULL  
      key_len: NULL  
          ref: NULL  
         rows: 3  
     filtered: 100.00  
        Extra: Using where  
*************************** 2. row ***************************  
           id: 2  
  select_type: DEPENDENT SUBQUERY  
        table: t2  
   partitions: NULL  
         type: ALL  
possible_keys: NULL  
          key: NULL  
      key_len: NULL  
          ref: NULL  
         rows: 6  
     filtered: 16.67  
        Extra: Using where  
mysql> SET @@optimizer_switch="subquery_to_derived=on";  
mysql> EXPLAIN SELECT * FROM t1  
    ->             WHERE   t1.b < 0  
    ->                     OR  
    ->                     EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1)\G  
*************************** 1. row ***************************  
           id: 1  
  select_type: PRIMARY  
        table: t1  
   partitions: NULL  
         type: ALL  
possible_keys: NULL  
          key: NULL  
      key_len: NULL  
          ref: NULL  
         rows: 3  
     filtered: 100.00  
        Extra: NULL  
*************************** 2. row ***************************  
           id: 1  
  select_type: PRIMARY  
        table: <derived2>  
   partitions: NULL  
         type: ALL  
possible_keys: NULL  
          key: NULL  
      key_len: NULL  
          ref: NULL  
         rows: 6  
     filtered: 100.00  
        Extra: Using where; Using join buffer (hash join)  
*************************** 3. row ***************************  
           id: 2  
  select_type: DERIVED  
        table: t2  
   partitions: NULL  
         type: ALL  
possible_keys: NULL  
          key: NULL  
      key_len: NULL  
          ref: NULL  
         rows: 6  
     filtered: 100.00  
        Extra: Using temporary

If we execute SHOW WARNINGS after runningEXPLAIN on the querySELECT * FROM t1 WHERE t1.b < 0 OR EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1) whensubquery_to_derived has been enabled, and simplify the second row of the result, we see that it has been rewritten in a form which resembles this:

SELECT a, b FROM t1  
LEFT JOIN (SELECT DISTINCT 1 AS e1, t2.a AS e2 FROM t2) d  
ON t1.a + 1 = d.e2  
WHERE   t1.b < 0  
        OR  
        d.e1 IS NOT NULL;

For more information, seeSection 10.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”, as well asSection 10.2.1.19, “LIMIT Query Optimization”, andSection 10.2.2.1, “Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations”.

When you assign a value tooptimizer_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,
                    derived_merge=on,use_invisible_indexes=off,skip_scan=on,
                    hash_join=on,subquery_to_derived=off,
                    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 can 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,
                    derived_merge=on,use_invisible_indexes=off,skip_scan=on,
                    hash_join=on,subquery_to_derived=off,
                    prefer_ordering_index=on