On Fri, 14 Sep 2007, Jeremy Cole wrote: > 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. Equally important, since the join order is
but experimentally using SELECT STRAIGHT_JOIN *does make* a difference. If I use it there is no time spent in the "statistics" phase, otherwise there is. Shawn can comment more conclusively since he knows the inner working of mysql. I just report a finding by experiment. > should have no effect whatsoever. 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. However the "correct order" is not always obvious to be determined a priori (like in my cases where I have a "glorified correlation" table G which shall go first, but all member tables which go next with equal rank (except the "first member" which is more equal than the other "=) ). The user can then build a query which uses only SOME of the members. And I've noticed that the result of EXPLAIN SELECT (the order) changed according to the content of the query (e.g. the particular WHERE condition). So for me experimentally use of SELECT STRAIGHT_JOIN is an effective solution. -- ----------------------------------------------------------------------- Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy) For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html ----------------------------------------------------------------------- () ascii ribbon campaign - against html mail /\ http://arc.pasp.de/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
