On Wed, 02 Jan 2008 18:02:46 +0100, gianpaolo <gianpaolo.pelloni@tin.it>
wrote:

> This is the query:
>
> SELECT DISTINCT A.id FROM B INNER JOIN A ON A.id=B.artist WHERE
> B.artist != 2444 AND B.artist != 92874 AND (A.surname LIKE 'james brown
> %' OR A.name LIKE 'james brown%') LIMIT 3;
>
> A: 230,848 rows
> B: 386,933 rows
>
> A has indexes on: id, name, surname
> B has indexes on: artist
>
> id: int 4
> name and surname: varchar 70
>
> Query execution time: 2.34 sec
>
> If i execute a query like this:
> SELECT DISTINCT A.id FROM B INNER JOIN A ON A.id=B.artist WHERE
> (A.surname LIKE 'james brown%' OR A.name LIKE 'james brown%') LIMIT 3;
> the execution time is: 0.00 sec
>
> I really don't understand. Anyone can help me please?

I'd guess, that the WHERE statement will severely limit the rowcount
returned from A, leaving little to scan for join on B. If you have the
WHERE clause of A in the JOIN syntax on B, it will not scan A first for
compliance with the join rule, it will try to join ALL A records to B
records, and only then check wether the fields in A match. It's a guess
though.
--
Rik Wasmus