Re: excessive time spent in "statistics" status

[Pete Harlan]

On Fri, Sep 14, 2007 at 01:33:51AM -0700, Jeremy Cole wrote:
> Hi Shawn, Lucio,
> 
> >SELECT STRAIGHT_JOIN ....
> >FROM ...
> >LEFT JOIN ...
> >WHERE ...
> >...
> 
> Just to correct a point here... if a query uses only LEFT JOIN or RIGHT 
> JOIN, the join order is fixed by the query's order itself, so using 
> STRAIGHT_JOIN should have no effect whatsoever.

True, because you say "only" left or right joins.

> Equally important, since the join order is fixed when you use LEFT
> JOIN, you *must* list the joins in the correct order when writing
> the query, otherwise you will see very poor performance.

"Only" is missing from here, which could be misleading.  MySQL will
reorder the t0, t1 and t2 joins in:

	select	...
	from	t0
			join t1 on ...
			join t2 on ...
			left join t3 on ...
	where	...

> MySQL's optimizer cannot reorder the joins because it has the potential 
> to change the result of the query.

Do have an example in mind?

Thanks,

--Pete