Performance impact of filtering in ON vs WHERE clause

[John McNally]

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)?