Copyright (C) 2000-2012 |
GNU Info (mysql.info)LEFT JOIN optimizationHow MySQL Optimizes `LEFT JOIN' and `RIGHT JOIN' ------------------------------------------------ `A LEFT JOIN B' in MySQL is implemented as follows: * The table `B' is set to be dependent on table `A' and all tables that `A' is dependent on. * The table `A' is set to be dependent on all tables (except `B') that are used in the `LEFT JOIN' condition. * All `LEFT JOIN' conditions are moved to the `WHERE' clause. * All standard join optimizations are done, with the exception that a table is always read after all tables it is dependent on. If there is a circular dependence then MySQL will issue an error. * All standard `WHERE' optimizations are done. * If there is a row in `A' that matches the `WHERE' clause, but there wasn't any row in `B' that matched the `LEFT JOIN' condition, then 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: `column_name IS NULL' in the `WHERE' part, where column_name is a column that is declared as `NOT NULL', then MySQL will stop searching after more rows (for a particular key combination) after it has found one row that matches the `LEFT JOIN' condition. `RIGHT JOIN' is implemented analogously as `LEFT JOIN'. The table read order forced by `LEFT JOIN' and `STRAIGHT JOIN' will help the join optimizer (which calculates in which order tables should be joined) to do its work much more quickly, as there are fewer table permutations to check. Note that the above means that if you do a query of type: SELECT * FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key) WHERE b.key=d.key MySQL will do a full scan on `b' as the `LEFT JOIN' will force it to be read before `d'. The fix in this case is to change the query to: SELECT * FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key) WHERE b.key=d.key automatically generated by info2www version 1.2.2.9 |