Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 46.8Mb
PDF (A4) - 46.9Mb
PDF (RPM) - 42.2Mb
HTML Download (TGZ) - 10.8Mb
HTML Download (Zip) - 10.8Mb
HTML Download (RPM) - 9.3Mb
Man Pages (TGZ) - 225.8Kb
Man Pages (Zip) - 331.3Kb
Info (Gzip) - 4.2Mb
Info (Zip) - 4.2Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions

8.2.2 Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions

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 (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 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.