MySQL Forums
Forum List  »  Optimizer & Parser

Performance impact of filtering in ON vs WHERE clause
Posted by: John McNally
Date: April 28, 2006 12:17PM

I have a question regarding two queries which should give identical results.

Tables:

Table A
-------
id int (primary key)
more columns...

Table B
-------
id int (primary key)
a_id int (foreign key reference to A.id)
more_columns...
index (a_id)

Assume A is large say 1 million rows and B on average contains about 100 rows per row in A.

The goal is to get 1 record in A and the associated rows in B. Two different queries that seem like they should return the same value are:


1. select * from A inner join B on (B.a_id = A.id) where B.a_id = 101;

2. select * from A inner join B on (B.a_id = A.id and B.a_id = 101);

The recommendation I see in the mysql docs seem to indicate (1) is preferred and I have seen this recommendation elsewhere. However it seems the only way (1) would perform as well as (2) is if the query optimizer reduced them to the same internal operations. My mental picture of (1) is the creation of a 100M joined table and the use of an index to then select the 100 rows from this temporary structure. For (2) it seems it would be possible to reduce the rows immediately to the 100 relevant ones during the joining of the tables.

And this example is pared down. Consider if there are a few more tables joined, here is one more:

1. select * from A inner join B on (B.a_id = A.id) left outer join C on (C.b_id = B.id) where B.a_id = 101;

2. select * from A inner join B on (B.a_id = A.id and B.a_id = 101) left outer join C on (C.b_id = B.id);

In this case it seems in (1) the join will again be on 100M rows, while in (2) the join to table C would only involve 100 rows.

Would (2) perform better? Is it possible that it could perform worse than (1)?

Options: ReplyQuote


Subject
Views
Written By
Posted
Performance impact of filtering in ON vs WHERE clause
15238
April 28, 2006 12:17PM


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.