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

SELECT * FROM t1 WHERE t1.column1 IN
(SELECT column1 FROM t2 ORDER BY column1);
SELECT * FROM t1 WHERE t1.column1 IN
(SELECT DISTINCT column1 FROM t2);
SELECT * FROM t1 WHERE EXISTS
(SELECT * FROM t2 LIMIT 1);

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

SELECT DISTINCT column1 FROM t1 WHERE t1.column1 IN (
SELECT column1 FROM t2);

Instead of this:

SELECT DISTINCT t1.column1 FROM t1, t2
WHERE t1.column1 = t2.column1;

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.8.11, “Rewriting Subqueries as Joins for Earlier MySQL Versions”.

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

SELECT * FROM t1
WHERE s1 IN (SELECT s1 FROM t1 UNION ALL SELECT s1 FROM t2);

Instead of this query:

SELECT * FROM t1
WHERE s1 IN (SELECT s1 FROM t1) OR s1 IN (SELECT s1 FROM t2);

For another example, use this query:

SELECT (SELECT column1 + 5 FROM t1) FROM t2;

Instead of this query:

SELECT (SELECT column1 FROM t1) + 5 FROM t2;

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

SELECT * FROM t1
WHERE (column1,column2) IN (SELECT column1,column2 FROM t2);

Instead of this query:

SELECT * FROM t1
WHERE EXISTS (SELECT * FROM t2 WHERE t2.column1=t1.column1
AND t2.column2=t1.column2);

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:

SELECT * FROM t1 WHERE t1.col_name
= (SELECT a FROM t2 WHERE b = some_const);

Instead of this query:

SELECT * FROM t1 WHERE t1.col_name
IN (SELECT a FROM t2 WHERE b = some_const);

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

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):

... IN (SELECT indexed_column FROM single_table ...)

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

value {ALL|ANY|SOME} {> | < | >= | <=} (non-correlated subquery)

For example, this WHERE clause:

WHERE 5 > ALL (SELECT x FROM t)

might be treated by the optimizer like this:

WHERE 5 > (SELECT MAX(x) FROM t)