(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
automatically generated byinfo2html