MySQL Forums
Forum List  »  Optimizer & Parser

Re: Performance impact of filtering in ON vs WHERE clause
Posted by: Björn Steinbrink
Date: May 02, 2006 02:21PM

For inner joins, I really can't see any semantic difference between having the conditions in the join part and having them in the where part of the query and the optimizer should just use either conditions whenever it thinks that it is appropriate. So for inner joins, I think that the difference is purely cosmetic, at least with MySQL as I remember that the manual for older MySQL versions said that join conditions for inner joins were simply treated as where conditions. And now it says "For each table in a join, a simpler WHERE is constructed to get a fast WHERE evaluation for the table and also to skip rows as soon as possible.". That still sounds to me like MySQL is treating both forms the same, using both join and where conditions when doing an inner join.

Of course for outer joins the situation is different, as semantics change when you exchange where conditions for join conditions.

Options: ReplyQuote

Written By
Re: Performance impact of filtering in ON vs WHERE clause
May 02, 2006 02:21PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.