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
Received on Mon May 1 03:07:26 2006