MySQL: "inner join on" performance: "where" vs adding to "on"

Question

Is there a performance difference between these two?

select * from tableA INNER JOIN tableB ON tableA.type = ? AND tableB.ref = tableA.id
select * from tableA INNER JOIN tableB ON tableB.ref = tableA.id WHERE tableA.type = ?

possible duplicate of [In MySQL queries, why use join instead of where?](http://stackoverflow.com/questions/2241991/in-mysql-queries-why-use-join-instead-of-where) — OMG Ponies, Feb 22 '11 at 17:23
Not quite the same. In this case I am combining the tables using join and the where statement is additional. The possible duplicate you stated actually referred to doing join's job with where which I am not doing. @OMG — 700 Software, Feb 22 '11 at 22:36

score 5 · Accepted Answer · answered Feb 22 '11 at 17:16

I'm guessing they will be identical but you can find out for yourself by running EXPLAIN on both of them and looking at the actual plan.

Generally with a declarative language like SQL you aren't going to be able to find silver bullet optimizations that are so simple. If there were a significant performance advantage for one over the other the optimizer would likely recognize it and run a query plan that used the most efficient technique.

MySQL: "inner join on" performance: "where" vs adding to "on"

1 Answers1