Why do I include some WHERE restrictions in my ON clauses and encourage others to do it, too? Because I believe that the WHERE clause is applied AFTER the temporary virtual table (the result of applying all of the JOIN and ON clauses in the full statement) is complete. That is also why I am such a strong proponent of using the explicit JOIN forms of INNER JOIN, RIGHT JOIN, and LEFT JOIN.
I believe that the ON clause in *each* JOIN works to reduce the size of the virtual table *as it is being constructed*. For me, the smaller the virtual table, the better the query's performance. If it doesn't work this way then how are we able to do a LEFT JOIN on two tables and look for null values from the right-hand table as a means of detecting non-matches? Why does the EXPLAIN of a JOIN show which indexes are being used for each new table if they aren't going to be used? Why is there a STRAIGHT JOIN modifier? I believe that if the order of the JOINs and the presence of the ON clauses were not important to performance then there wouldn't be so many tools to help us optimize their performance. Humbly, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Michael Stassen <[EMAIL PROTECTED]> wrote on 07/30/2004 05:42:24 PM: > Why do you expect moving the a.timestamp restriction from the WHERE clause > to the JOIN will help? > > Michael > > [EMAIL PROTECTED] wrote: > > > Yes, I can think of two things you can try in order to speed up your > > query. First - try the STRAIGHT JOIN clause with one small but critical > > change to your statement(http://dev.mysql.com/doc/mysql/en/SELECT.html): > > > > SELECT STRAIGHT JOIN * > > FROM a > > LEFT JOIN b ON a.a_id = b.a_id > > AND a.timestamp BETWEEN 20040101000000 AND 20040101235959 > > JOIN c ON a.c_id = c.c_id > > JOIN d ON c.d_id = d.d_id > > JOIN e ON c.e_id = e.e_id > > GROUP BY c.d_id, c.e_id > > > > The second thing to try would be to pre-calculate (hand optimize) the > > different stages of your query (don't join all of the tables together all > > at once). Although, when I started to do it myself, I was mimicking what > > the STRAIGHT JOIN should do for us automatically. So I guess I really have > > only one suggestion, hmmph... > > > > It worked wonders for someone else recently, I think it's what you need > > for this situation. > > > > Shawn Green > > Database Administrator > > Unimin Corporation - Spruce Pine > >
