How to optimize mysql query getting categories and subcategories
Solution 1
Try this query -
categories+sub categories:
SELECT c1.id, c1.name, c2.id, c2.name FROM categories c1
LEFT JOIN categories c2
ON c2.parent_id = c1.id
WHERE c1.parent_id = 0
categories+sub categories+sub sub categories:
SELECT c1.id, c1.name, c2.id, c2.name, c3.id, c3.name FROM categories c1
LEFT JOIN categories c2
ON c2.parent_id = c1.id
LEFT JOIN categories c3
ON c3.parent_id = c2.id
WHERE c1.parent_id = 0
Solution 2
Some database servers have constructs to help these operations. However, MySQL is not one of them.
There are alternative ways to store hierarchies in a database that are more efficient. For a full story, check out this article. It will introduce you to nested sets.
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
Comments
-
Green Cat almost 2 years
I created a script which builds a menu with categories, sub-categories and sub-sub-categories. To get these items I use 3 separate MySQL queries. As follows:
To retrieve the main categories:
SELECT id, name FROM categories WHERE parent_id=0
To retrieve the sub-categories for each category I use:
SELECT id, name FROM categories WHERE parent_id=?
Where the ? is the id of the parent category. I then run the query again for each sub-category.
How can I optimise this script to use less sql queries ? Thank you in advance.