Query running very very slow

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?

gianpaolo <gianpaolo.pelloni@tin.it> wrote in news:df74318e-974f-4b75-97d5-
3c15ba82b5fa@y5g2000hsf.googlegroups.com:

> 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?
>

Try using EXPLAIN to see a little more of what is happening behind the
scenes....

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

gianpaolo 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?

how about something like this:

select distinct
a.id
from
a inner join b 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;

switching a and b on the join.

plus add another index where name and surname (in that order) are
members of the same index field.

lark wrote:
> gianpaolo 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?
>
> how about something like this:
>
> select distinct
> a.id
> from
> a inner join b 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;
>
> switching a and b on the join.
>
> plus add another index where name and surname (in that order) are
> members of the same index field.

If the data that OP needs only comes from table a, then it would be better to use:

SELECT DISTINCT id
FROM A
WHERE id NOT IN (2444,92874)
AND (surname LIKE 'james brown%' OR name LIKE 'james brown%')
LIMIT 3;

As A.id=B.artist

--

//Aho

On Jan 2, 9:16 pm, "J.O. Aho" <u...@example.net> wrote:
> lark wrote:
> > gianpaolo 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?
>
> > how about something like this:
>
> > select distinct
> > a.id
> > from
> > a inner join b 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;
>
> > switching a and b on the join.
>
> > plus add another index where name and surname (in that order) are
> > members of the same index field.
>
> If the data that OP needs only comes from table a, then it would be better to use:
>
> SELECT DISTINCT id
> FROM A
> WHERE id NOT IN (2444,92874)
> AND (surname LIKE 'james brown%' OR name LIKE 'james brown%')
> LIMIT 3;
>
> As A.id=B.artist
>
> --
>
> //Aho

I have to be certain that the id returned is in B.artist and that the
name or surname matches A.
Probably if i split the query it will run faster.
I'll try like this:
SELECT DISTINCT id
FROM A
WHERE id NOT IN (2444,92874)
AND (surname LIKE 'james brown%' OR name LIKE 'james brown%')
LIMIT 3;

And check the IDs returned against B.artist.

Thanks.

Happy new Year!!!!!!!!