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