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, 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 a SELECT , WHERE , JOIN , or HAVING  clause into a left outer joins 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 as RAND() . 

•The subquery is not an ANY  or ALL  subquery which can be rewritten to use MIN()  or MAX() . 

•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 a GROUP BY  clause. 

This optimization can also be applied to a table subquery which is the argument to IN , NOT IN , EXISTS , or NOT EXISTS , that does not contain a GROUP BY . 

The default value for this flag is off , 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 the subquery_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); 

