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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
