SELECT WHERE col1 IN() OR col2 IN()

[Antnee]

What's the best way to do this? We're passing a large number of IDs essentially like this: (simplified query)

SELECT
  *
FROM
  table
WHERE
  col1 IN (1, 2, 3, 4, 5...)
OR
  col2 IN (1, 2, 3, 4, 5...)

The actual query is quite large and joins three tables. An explain doesn't look very good:

id  select_type  table  type    possible_keys                    key              key_len  ref       rows   Extra
1   SIMPLE       ph     ALL     PRIMARY,IX_basephid              NULL             NULL     NULL      36250  Using where; Using temporary
1   SIMPLE       s      ref     phid                             phid             5        db.ph.id  1      Using index
1   SIMPLE       p      ref     IX_products_pid,IX_products_tid  IX_products_pid  5        db.ph.id  37     Using where; Distinct
1   SIMPLE       t      eq_ref  PRIMARY,catid                    PRIMARY          4        db.p.tid  1      Using where; Distinct

Here's the actual query:

SELECT DISTINCT
    ph.id phid
,   ph.basephid baseid
,   s.id stockid
FROM
    phones ph
INNER
    JOIN p
    ON ph.id = p.pid
INNER
    JOIN t
    ON t.id = p.tid
LEFT
    JOIN s
    ON ph.id = s.phid
WHERE
    ph.active = 1
AND t.active  = 1
AND t.paytype = 1
AND t.catid   = 3
AND (
    ph.basephid IN (1, 2 ,3 ,4, 5, 6)
OR
    ph.id IN (1, 2, 3, 4, 5, 6)
)

It strikes me that the final "AND (col1 IN() OR col2 IN())" is the bit that is really slowing this down but would appreciate your advice guys.

Thanks

[r937]

try breaking up the query into two SELECTS, one for one IN condition and the other for the other, then combine the SELECTs with UNION

also, are you sure you need DISTINCT?

[Antnee]

Tried UNION and it seemed slower but may have done it badly. Will try it again.

Also, regarding DISTINCT, we either need that or we need to GROUP BY ph.id