MySQL 8.0 Release Notes
MySQL 8.0 Source Code Documentation
[+/-]
The MySQL query optimizer has different strategies available to evaluate subqueries:
For
IN(or=ANY) subqueries, the optimizer has these choices:Semi-join
Materialization
EXISTSstrategy
For
NOT IN(or<>ALL) subqueries, the optimizer has these choices:Materialization
EXISTSstrategy
For derived tables, the optimizer has these choices (which also apply to view references and common table expressions):
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.
Note
A limitation on UPDATE and
DELETE statements that use a
subquery to modify a single table is that the optimizer does
not use semi-join 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.