MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
The MySQL query optimizer has different strategies available to evaluate subqueries:
For IN
(or =ANY
)
subqueries, the optimizer has these choices:
Semijoin
Materialization
EXISTS
strategy
For NOT IN
(or
<>ALL
) subqueries, the optimizer
has these choices:
Materialization
EXISTS
strategy
For derived tables, the optimizer has these choices:
Merge the derived table into the outer query block
Materialize the derived table to an internal temporary table
The following discussion provides more information about the preceding optimization strategies.
A limitation on UPDATE
and
DELETE
statements that use a
subquery to modify a single table is that the optimizer does
not use semijoin or materialization subquery optimizations. As
a workaround, try rewriting them as multiple-table
UPDATE
and
DELETE
statements that use a
join rather than a subquery.