(mysql.info.gz) Optimizing subqueries

(mysql.info.gz) Rewriting subqueries
 
 13.1.8.10 Optimizing Subqueries
 ...............................
 
 Development is ongoing, so no optimization tip is reliable for the long
 term. Some interesting tricks that you might want to play with are:
 
    * 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, use this query:
 
           SELECT DISTINCT column1 FROM t1 WHERE t1.column1 IN (
             SELECT column1 FROM t2);
 
      Instead of this query:
 
           SELECT DISTINCT t1.column1 FROM t1, t2
             WHERE t1.column1 = t2.column1;
 
    * 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'.
 
 These tricks might cause programs to go faster or slower. Using MySQL
 facilities like the `BENCHMARK()' function, you can get an idea about
 what helps in your own situation. Don't worry too much about
 transforming to joins except for compatibility with older versions of
 MySQL before 4.1 that do not support subqueries.
 
 Some optimizations that MySQL itself makes are:
 
    * 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'/`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' will describe as a special
      join type:
 
           ... 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)
 
 
 There is a chapter titled "How MySQL Transforms Subqueries" in the
 MySQL Internals Manual. You can obtain this document by downloading the
 MySQL source package and looking for a file named `internals.texi' in
 the `Docs' directory.
Info Catalog
(mysql.info.gz) Subquery errors
(mysql.info.gz) Subqueries
(mysql.info.gz) Rewriting subqueries
automatically generated byinfo2html