Slow query.

[timgolding]

I have the job of combing the databases from lots of smaller sites (all with same database structure) in to one big central database. This was going fine until i came across the first query that is really slow.

SELECT * , RANGE_ID AS ID
FROM SITES_RANGE
WHERE SITE_ID =00001
AND RANGE_ID
IN (

SELECT DISTINCT RANGE_ID
FROM SITES_PRODUCT
WHERE CATEGORY_ID = '00009'

)
ORDER BY ORDERING DESC

While the sub query takes only 0.13 seconds. The whole query combined takes 3.5 seconds. Which is too slow i think. How can i speed it up. The query basically checks product to select all the ranges that belong to a certain category for that site. Can i add an index to my tables? And if so what fields should i add the indexes to?

[timgolding]

I tried using a join query instead of the sub query in the end

SELECT SITES_RANGE . * , SITES_RANGE.RANGE_ID AS ID
FROM SITES_RANGE
JOIN SITES_PRODUCT ON SITES_RANGE.RANGE_ID = SITES_PRODUCT.RANGE_ID
AND SITES_PRODUCT.CATEGORY_ID = '00001'
GROUP BY ID

Speed was 0.11 secs

Problem solved