Subqueries come in several flavors, and they have different optimization potential. First, note that subqueries can be either "correlated" or "uncorrelated". Correlated means that they depend on some value from outside the subquery. This generally implies that the subquery must be re-evaluated for each outer value.
This correlated subquery is often pretty good. Note: It must return at most 1 value. It is often useful as an alternative to, though not necessarily faster than, a
SELECT a, b, ( SELECT ... FROM t WHERE t.x = u.x ) AS c FROM u ... SELECT a, b, ( SELECT MAX(x) ... ) AS c FROM u ... SELECT a, b, ( SELECT x FROM t ORDER BY ... LIMIT 1 ) AS c FROM u ...
This is usually uncorrelated:
SELECT ... FROM ( SELECT ... ) AS a JOIN b ON ...
Notes on the
( SELECT @n := 0 ), thereby initializing an `@variable for use in the rest or the query.
( SELECT ... )with many rows, then efficiency can be terrible. Pre-5.6, there was no index, so it became a
CROSS JOIN; 5.6+ involves deducing the best index on the temp tables and then generating it, only to throw it away when finished with the