MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide

8.2.2 Optimizing Subqueries and Derived Tables

The MySQL query optimizer has different strategies available to evaluate subqueries:

For derived tables, the optimizer has these choices:

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.