7.2.9. LEFT JOIN and RIGHT JOIN Optimization
MySQL implements an A LEFT
JOIN B join_condition
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
NULLWHERE 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.
