Re: excessive time spent in "statistics" status

[Baron Schwartz]

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