Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.4Mb
PDF (A4) - 37.4Mb
PDF (RPM) - 37.0Mb
EPUB - 10.6Mb
HTML Download (TGZ) - 10.3Mb
HTML Download (Zip) - 10.4Mb
HTML Download (RPM) - 9.0Mb
Eclipse Doc Plugin (TGZ) - 11.2Mb
Eclipse Doc Plugin (Zip) - 13.4Mb
Man Pages (TGZ) - 204.7Kb
Man Pages (Zip) - 311.8Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Optimizing Subqueries, Derived Tables, and View References

9.2.2 Optimizing Subqueries, Derived Tables, and View References

The MySQL query optimizer has different strategies available to evaluate subqueries. For IN (or =ANY) subqueries, the optimizer has these choices:

  • Semi-join

  • Materialization

  • EXISTS strategy

For NOT IN (or <>ALL) subqueries, the optimizer has these choices:

  • Materialization

  • EXISTS strategy

For derived tables (subqueries in the FROM clause), the optimizer has these choices:

  • Merge the derived table into the outer query block

  • Materialize the derived table to an internal temporary table

For view references, the optimizer has the same choices as for derived tables.

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.


User Comments
Sign Up Login You must be logged in to post a comment.