Movatterモバイル変換

The optimizer uses materialization to enable more efficient subquery processing. Materialization speeds up query execution by generating a subquery result as a temporary table, normally in memory. The first time MySQL needs the subquery result, it materializes that result into a temporary table. Any subsequent time the result is needed, MySQL refers again to the temporary table. The optimizer may index the table with a hash index to make lookups fast and inexpensive. The index contains unique values to eliminate duplicates and make the table smaller.

Subquery materialization uses an in-memory temporary table when possible, falling back to on-disk storage if the table becomes too large. SeeSection 10.4.4, “Internal Temporary Table Use in MySQL”.

If materialization is not used, the optimizer sometimes rewrites a noncorrelated subquery as a correlated subquery. For example, the followingIN subquery is noncorrelated (where_condition involves only columns fromt2 and nott1):

SELECT * FROM t1WHERE t1.a IN (SELECT t2.b FROM t2 WHEREwhere_condition);

The optimizer might rewrite this as anEXISTS correlated subquery:

SELECT * FROM t1WHERE EXISTS (SELECT t2.b FROM t2 WHEREwhere_condition AND t1.a=t2.b);

Subquery materialization using a temporary table avoids such rewrites and makes it possible to execute the subquery only once rather than once per row of the outer query.

For subquery materialization to be used in MySQL, theoptimizer_switch system variablematerialization flag must be enabled. (SeeSection 10.9.2, “Switchable Optimizations”.) With thematerialization flag enabled, materialization applies to subquery predicates that appear anywhere (in the select list,WHERE,ON,GROUP BY,HAVING, orORDER BY), for predicates that fall into any of these use cases:

The following examples illustrate how the requirement for equivalence ofUNKNOWN andFALSE predicate evaluation affects whether subquery materialization can be used. Assume thatwhere_condition involves columns only fromt2 and nott1 so that the subquery is noncorrelated.

This query is subject to materialization:

SELECT * FROM t1WHERE t1.a IN (SELECT t2.b FROM t2 WHEREwhere_condition);

Here, it does not matter whether theIN predicate returnsUNKNOWN orFALSE. Either way, the row fromt1 is not included in the query result.

An example where subquery materialization is not used is the following query, wheret2.b is a nullable column:

SELECT * FROM t1WHERE (t1.a,t1.b) NOT IN (SELECT t2.a,t2.b FROM t2                          WHEREwhere_condition);

The following restrictions apply to the use of subquery materialization:

Use ofEXPLAIN with a query provides some indication of whether the optimizer uses subquery materialization:

MySQL can also apply subquery materialization to a single-tableUPDATE orDELETE statement that uses a[NOT] IN or[NOT] EXISTS subquery predicate, provided that the statement does not useORDER BY orLIMIT, and that subquery materialization is allowed by an optimizer hint or by theoptimizer_switch setting.