[RESOLVED] Slow SQL queries

[shawjames]

I have been trying to clean up my SQL queries, and am stuck on something very simple. This query:

select * from ds_products where catID = '774' or cat2ID = '774'

I have both catID and cat2ID indexed, but this query does not use either. EXPLAIN shows that

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE ds_products ALL catID,cat2ID NULL NULL NULL 23378 Using where

key = NULL

How should I setup my index so that this indexes properly? Or do I need to fix this before the query is made so that I dont use the OR?

[nemonoman]

Your OR is causing each row to be reviewed individually.

If speed is an issue, consider creating another field when records are inserted or updated, an indexed TINYINT = 1 if cat1 or cat2 =774

INSERT INTO sometable SET search774=if(cat1=774 OR cat2=774, 1, 0)

ALTERNATIVELY

do 2 selects

Select * from ds_products where catID = '774'
read all the returned rows into an array

then
Select * from ds_products where cat2ID = '774'
read all the returned rows into that SAME array

Now you have OR'd the records

And those will use indexes and be fast