Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.2Mb
PDF (A4) - 31.2Mb
PDF (RPM) - 29.5Mb
HTML Download (TGZ) - 7.3Mb
HTML Download (Zip) - 7.3Mb
HTML Download (RPM) - 6.2Mb
Man Pages (TGZ) - 179.4Kb
Man Pages (Zip) - 289.5Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  ...  /  Optimizing Subqueries and Derived Tables

8.2.2 Optimizing Subqueries and Derived Tables

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.

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.