Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

MYSQL Slow Nested Set Categories to Products Query

Avatar of stilliard
stilliardFlag for United Kingdom of Great Britain and Northern Ireland asked on
PHPMySQL ServerSQL
10 Comments1 Solution1129 ViewsLast Modified:
Hi all,
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,
  IF((
    SELECT node.id
    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
ASKER CERTIFIED SOLUTION
Avatar of Lowfatspread
Commented:
This problem has been solved!
Unlock 1 Answer and 10 Comments.
See Answers