(mysql.info.gz) LEFT JOIN optimization

Info Catalog

(mysql.info.gz) DISTINCT optimization

(mysql.info.gz) Query Speed

(mysql.info.gz) ORDER BY optimization

 
 7.2.9 How MySQL Optimizes `LEFT JOIN' and `RIGHT JOIN'
 ------------------------------------------------------
 
 `A LEFT JOIN B join_condition' is implemented in MySQL 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 done, 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 done.
 
    * 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 don't 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.
 
 `RIGHT JOIN' is implemented analogously to `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' and `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 will do 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 to rewrite the query as follows:
 
      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;
 
 Starting from 4.0.14, MySQL does the following `LEFT JOIN' optimization:
 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' would be `NULL':
 
      SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;
 
 Therefore, it's 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 this would result in a better query plan.  To force a specific
 table order, use `STRAIGHT_JOIN'.
 

Info Catalog

(mysql.info.gz) DISTINCT optimization

(mysql.info.gz) Query Speed

(mysql.info.gz) ORDER BY optimization