8.13.16.2. Optimizing Subqueries with Subquery Materialization

As of MySQL 5.6.5, the optimizer uses subquery materialization as a strategy that enables more efficient subquery processing.

If materialization is not used, the optimizer sometimes rewrites a noncorrelated subquery as a correlated subquery. For example, the following IN subquery is noncorrelated (where_condition involves only columns from t2 and not t1):

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

The optimizer might rewrite this as an EXISTS correlated subquery:

SELECT * FROM t1WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_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. 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 table is indexed with a hash index to make lookups fast and inexpensive. The index is unique, which makes the table smaller because it has no duplicates.

Subquery materialization attempts to use an in-memory temporary table when possible, falling back to on-disk storage if the table becomes too large. See Section 8.4.3.3, "How MySQL Uses Internal Temporary Tables".

For subquery materialization to be used in MySQL, the materialization flag of the optimizer_switch system variable must be on. Materialization then applies to subquery predicates that appear anywhere (in the select list, WHERE, ON, GROUP BY, HAVING, or ORDER BY), for predicates that fall into any of these use cases:

The predicate has this form, when no outer expression oe_i or inner expression ie_i is nullable. N can be 1 or larger.
```
(oe_1, oe_2, ..., oe_N) [NOT] IN (SELECT ie_1, i_2, ..., ie_N ...)
```
The predicate has this form, when there is a single outer expression oe and inner expression ie. The expressions can be nullable.
```
oe [NOT] IN (SELECT ie ...)
```
The predicate is IN or NOT IN and a result of UNKNOWN (NULL) has the same meaning as a result of FALSE.

The following examples illustrate how the requirement for equivalence of UNKNOWN and FALSE predicate evaluation affects whether subquery materialization can be used. Assume that where_condition involves columns only from t2 and not t1 so that the subquery is noncorrelated.

This query is subject to materialization:

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

Here, it does not matter whether the IN predicate returns UNKNOWN or FALSE. Either way, the row from t1 is not included in the query result.

An example where subquery materialization will not be used is the following query, where t2.b is a nullable column.

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

Use of EXPLAIN with a query can give some indication of whether the optimizer uses subquery materialization. Compared to query execution that does not use materialization, select_type may change from DEPENDENT SUBQUERY to SUBQUERY. This indicates that, for a subquery that would be executed once per outer row, materialization enables the subquery to be executed just once. In addition, for EXPLAIN EXTENDED, the text displayed by a following SHOW WARNINGS will include materialize materialize and materialized-subquery (materialized subselect before MySQL 5.6.6).