Wondered if anyone could help me speed up this sql query im working on.
The basis of the query is to list categories from a table who have products relating to them, or categories relating to them who at some nested sub level will eventually relate to products.
To do this i was using a parent-child sort of adjacency list model, but i have now added support for the nested set model, using left right and level columns, so i can easily select all categories and sub categories relating to a category and then check if they are the parent category of any products.
The code im using i have attempted to optimize but im very new to using this "nested list model" and so im not 100% im using it correctly, or maybe not efficiently.
The query has to run on arround 1,000 categories, and arround 30,000 products. But its taking minutes to load.
Any Ideas to change the sql, or go about i differently are welcome!
SELECT parent.id, parent.category_name, parent.relatedid, parent.sort_order,
FROM categories node
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND EXISTS (
SELECT null FROM products p
WHERE p.category = node.id
LIMIT 0, 1
) > 0, 1, 0) AS productsFound
FROM categories parent
HAVING productsFound > 0
ORDER BY ABS(parent.sort_order), parent.sort_order, parent.category_name, parent.id