(mysql.info) left-join-optimization

(mysql.info) distinct-optimization
 
 7.2.9 `LEFT JOIN' and `RIGHT JOIN' Optimization
 -----------------------------------------------
 
 MySQL implements an `A LEFT JOIN B join_condition' as follows:
 
    * Table B is set to depend on table A and all tables on which A
      depends.
 
    * Table A is set to depend on all tables (except B) that are used in
      the `LEFT JOIN' condition.
 
    * The `LEFT JOIN' condition is used to decide how to retrieve rows
      from table B. (In other words, any condition in the `WHERE' clause
      is not used.)
 
    * All standard join optimizations are performed, with the exception
      that a table is always read after all tables on which it depends.
      If there is a circular dependence, MySQL issues an error.
 
    * All standard `WHERE' optimizations are performed.
 
    * If there is a row in A that matches the `WHERE' clause, but there
      is no row in B that matches the `ON' condition, an extra B row is
      generated with all columns set to `NULL'.
 
    * If you use `LEFT JOIN' to find rows that do not exist in some
      table and you have the following test: `COL_NAME IS NULL' in the
      `WHERE' part, where COL_NAME is a column that is declared as `NOT
      NULL', MySQL stops searching for more rows (for a particular key
      combination) after it has found one row that matches the `LEFT
      JOIN' condition.
 
 The implementation of `RIGHT JOIN' is analogous to that of `LEFT JOIN'
 with the roles of the tables reversed.
 
 The join optimizer calculates the order in which tables should be
 joined. The table read order forced by `LEFT JOIN' or `STRAIGHT_JOIN'
 helps the join optimizer do its work much more quickly, because there
 are fewer table permutations to check. Note that this means that if you
 do a query of the following type, MySQL does a full scan on `b' because
 the `LEFT JOIN' forces it to be read before `d':
 
      SELECT *
        FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)
        WHERE b.key=d.key;
 
 The fix in this case is reverse the order in which `a' and `b' are
 listed in the `FROM' clause:
 
      SELECT *
        FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)
        WHERE b.key=d.key;
 
 For a `LEFT JOIN', if the `WHERE' condition is always false for the
 generated `NULL' row, the `LEFT JOIN' is changed to a normal join. For
 example, the `WHERE' clause would be false in the following query if
 `t2.column1' were `NULL':
 
      SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;
 
 Therefore, it is safe to convert the query to a normal join:
 
      SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;
 
 This can be made faster because MySQL can use table `t2' before table
 `t1' if doing so would result in a better query plan. To force a
 specific table order, use `STRAIGHT_JOIN'.
Info Catalog
(mysql.info) distinct-optimization
(mysql.info) query-speed
(mysql.info) nested-joins
automatically generated byinfo2html