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