a little condition doubt

[eric1235711]

hello, do these querys return the same data?

SELECT id
FROM mydb1 M1
INNER JOIN mydb2 M2
ON M1.id = M2.id
WHERE M2.field1 = 'test';

SELECT id
FROM mydb1 M1
INNER JOIN mydb2 M2
ON M1.id = M2.id
AND M2.field1 = 'test';


is the second one faster?

[shoz]

First, let me quote the MYSQL docs

[quote=http://dev.mysql.com/doc/refman/4.1/en/join.html]
You should generally not have any conditions in the ON part that are used to restrict which rows you want in the result set, but rather specify these conditions in the WHERE clause. There are exceptions to this rule.
[/quote]

I usually follow that rule unless the query requires that I not, meaning that I'd use query 1.

Both queries should return the same results in this instance and as far as which is faster I'm not sure. It'll probably depend on the contents of, and the indexes on the table.

MYSQL may see both queries in the same way and therefore search for the result in the same way.

This is my interpretation of both queries and keep in mind that when you use different JOINs (for instance a LEFT JOIN) this will matter.

In query 1 you're asking MYSQL to JOIN both tables where the id in M1 is same as the id in M2 and then to return a result WHERE field1 in M2 is the same as "test".

In query 2 you're asking MYSQL to only JOIN both tables where the id in M1 is the same as the id in M2 and the field in M2 is the same as "test".

This means that in query 1 you've explicitly restricted MYSQL to only return rows in the result where M2.field = "test" while in query 2 you've implicitly made the restriction with the "INNER JOIN".

EDIT: Reworded the comment.

[eric1235711]

but that quote of mysql dev is saying that using conditions in JOIN clause may cause problems in the result?


I find that the first way would load in memory all the 'joinings'  before filter all the data. And in the second way, it would apply the conditions before making the joining.

With tables with a lot of rows, the second way would be faster... but I don´t know if Mysql do the both exactly in the same way, or it do the query exactly as hte specified order...

[shoz]

[quote author=eric1235711 link=topic=114261.msg464972#msg464972 date=1163008182]
but that quote of mysql dev is saying that using conditions in JOIN clause may cause problems in the result?


I find that the first way would load in memory all the 'joinings'  before filter all the data. And in the second way, it would apply the conditions before making the joining.

With tables with a lot of rows, the second way would be faster... but I don´t know if Mysql do the both exactly in the same way, or it do the query exactly as hte specified order...
[/quote]

In the first query If MYSQL chooses to start with table M2 when performing the JOIN then it'll filter the data in M2 first before doing the JOIN. MYSQL won't necessarily do the JOIN in the order or in the "way" that you've written it.

MYSQL is usually smart about how it does a JOIN. Again depending on the contents of and the index on the table it may or may not be faster.

If you want to learn more about how MYSQL is going to perform a JOIN or any SELECT query for that matter do an [url=http://dev.mysql.com/doc/refman/4.1/en/explain.html]EXPLAIN [/url].

[fenway]

I've fussed with this over the years... for LEFT JOINs, it would matter, and in fact, that's when the exception comes up: when you want to filter on a table that as a result of the LEFT JOIN would null out the values by the time the WHERE clause was evaluated.