Just a quick note to add to this conversation:
Pete Harlan wrote:
On Fri, Sep 14, 2007 at 01:33:51AM -0700, Jeremy Cole wrote:
MySQL's optimizer cannot reorder the joins because it has the potential
to change the result of the query.
Not in all cases. If a LEFT JOIN query also has a WHERE clause that
disallows NULL rows from the right-hand table, it will (may? I'm not
sure if it always will, but at least some times it will) treat it as an
INNER JOIN, and it's therefore a candidate for reordering. I've also
seen cases where a LEFT JOIN becomes a CROSS JOIN
(http://bugs.mysql.com/bug.php?id=30842).
All RIGHT JOIN are rewritten to equivalent LEFT JOIN, so the same
optimizations can apply.
As a side note, I've really benefited from attending Timour's talk on
the optimizer at the conference this spring. His slides are good
reading too:
http://conferences.oreillynet.com/presentations/mysql07/katchaounov_timour.pdf
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
