Slow query with OR condition.

[DustyB]

I'm generating a complicated query where I am searching for someone who wants to work in a particular region or town.
Because of this I need to match on people who will work in either of thes cases so I am using an OR condition in my query. This is absolutely killing the query. There are approx 100,000 rows in the region table and approx 33,000 rows in the town table

I'm using MySQL 3.23.49 which doesn't have the luxury of a UNION statement. The query is as follows :-

select distinct(p.pers_id), p.surname, p.firstname
from person_wants_towns pwt,
person_wants_regions pwr,
persons p
where ((p.pers_id = pwt.pers_id and pwt.town_id=1) or (p.pers_id = pwr.pers_id and (pwr.region_id=1 or pwr.region_id=0)))
and p.pers_id=100001;

There is only one row on the persons table matching pers_id 100001.

This gives the following explain plan

p const PRIMARY PRIMARY 4 const 1 Using temporary
pwt index PRIMARY,pwt_town_idx,pwt_per_idx PRIMARY 6 NULL 33658 Using index; Distinct
pwr index PRIMARY,pwr_region_idx,pwr_per_idx PRIMARY 6 NULL 100038 where used; Using index; Distinct

If I change the OR to an AND then the response is instantaneous. What I don't understand is why the 'ref' column above is set to NULL when the primary key of all tables contains the pers_id. Surely it should eliminate all except one row in the first instance (which it is doing) and then use the pers_id from this to speed through the rest of the data rather than doing a full scan through both indexes ?

Has anyone got any ideas about this ?

 

[sleipnir214]

Take a look here:

http://www.mysql.com/doc/S/e/Searching_on_two_keys.html

Your problem seems one case of the general problem the documentation describes. I think the workaround documented there might be useful to you, too. ______________________________________________________________________
Never forget that we are
made of the stuff of stars

 

[DustyB]

Thanks for this. We actually truid this late last night and the problem has now gone away. Hopefully using UNION with MySQL 4 will tidy up this area !