Re: to optimize a select join

Jean-Claude wrote:

which is the faster query ? (of course, in my case the real queries
are more complex)

Which means exactly one thing: you must benchmark your real queries.
Read up on EXPLAIN statement.

1/
select *
from file1 a join file2 b on b.key=a.key
where b.data=123
and b.name='TEST'

2/
select *
from file1 a join file2 b on b.key=a.key and b.data=123
where b.name='TEST'

Off the top of my head, this is not going to make a lot of difference.
You might, however, want to experiment with "file1 AS a LEFT JOIN file2
AS b" vs. "file2 AS b LEFT JOIN file1 AS a". In your case, since you
filter by b.data and b.test, "file2 AS b LEFT JOIN file1 AS a" may be
faster, because MySQL will not be doing a full scan of file2.

Also, be sure that file1.key, file2.key, file2.data, and file2.name are
indexed.

Cheers,
NC

.