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