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.
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?Code: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