More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
How 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
(Continued on next question...)
