In and Exist are both slow. Anything else?
Hi everyone,
I posted here once before when my own research was not enough. I am hoping you can all help me again. In the following query I am trying to return all categories containing matching products and the # of products in each category. The query is accurate, but painfully slow. I have tried "IN" and "Exists" and both are slow. Does anyone see anything really obviously wrong that I have missed?
Code:
SELECT COUNT(categories.idcategory) AS ProductsInCategory, categories.idcategory, categories.categoryDesc
FROM (categories_products INNER JOIN categories ON categories_products.idcategory=categories.idcategory)
LEFT OUTER JOIN products as A ON A.idProduct=categories_products.idProduct
WHERE ((A.serviceSpec<>0 AND A.pcProd_BTODefaultPrice>=0 And A.pcProd_BTODefaultPrice<=999999999) OR (A.serviceSpec=0 AND A.price>=0 And A.price<=999999999))
AND A.active=-1
AND A.removed=0
AND categories.iBTOhide=0
AND categories.pccats_RetailHide=0
AND configOnly=0
AND A.idproduct IN (SELECT E.idproduct FROM pcSearchFields_Products E WHERE E.idSearchData=2135)
AND A.idproduct IN (SELECT E.idproduct FROM pcSearchFields_Products E WHERE E.idSearchData=2869)
GROUP BY categories.idcategory, categories.categoryDesc
ORDER BY categories.idcategory;