Using MySQL 5.0.45
In the MySQL manual page on left join optimization (http://dev.mysql.com/doc/refman/5.0/en/left-join-optimization.html), it says that in the query:
FROM a JOIN b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)
MySQL has to perform a full table scan because of the read order forced by left join.
I have a similar query, except that
1) All joins are explicitly left joins
2) All join conditions are given
It looks something like this:
SELECT a.*, b.*, c.*, d.*
FROM a LEFT JOIN b
ON a.primary_key = b.primary_key
LEFT JOIN c
ON a.primary_key = c.primary_key
LEFT JOIN d
ON a.primary_key = d.primary_key
WHERE b.other_key = other_key_value;
The way I think/hope MySQL does this is to join based on what is in table a using the primary key indexes in a, b, c, and d, and then narrow that result set based on b.other_key, with no full table scans.
Am I doing what I want, or am I full table scanning table b?
Thank you for your help!