13.2.11.11 Optimizing Subqueries

Use subquery clauses that affect the number or order of the rows in the subquery. For example:

Replace a join with a subquery. For example, try this:

Instead of this:

Some subqueries can be transformed to joins for compatibility with older versions of MySQL that do not support subqueries. However, in some cases, converting a subquery to a join may improve performance. See Section 13.2.11.12, “Rewriting Subqueries as Joins”.

Move clauses from outside to inside the subquery. For example, use this query:

Instead of this query:

For another example, use this query:

Instead of this query:

Use a row subquery instead of a correlated subquery. For example, use this query:

Instead of this query:

Use NOT (a = ANY (...)) rather than a <> ALL (...).

Use x = ANY (table containing (1,2)) rather than x=1 OR x=2.

Use = ANY rather than EXISTS.

For uncorrelated subqueries that always return one row, IN is always slower than =. For example, use this query:

Instead of this query:

MySQL executes uncorrelated subqueries only once. Use EXPLAIN to make sure that a given subquery really is uncorrelated.

MySQL rewrites IN, ALL, ANY, and SOME subqueries in an attempt to take advantage of the possibility that the select-list columns in the subquery are indexed.

MySQL replaces subqueries of the following form with an index-lookup function, which EXPLAIN describes as a special join type (unique_subquery or index_subquery):

MySQL enhances expressions of the following form with an expression involving MIN() or MAX(), unless NULL values or empty sets are involved:

For example, this WHERE clause:

might be treated by the optimizer like this: