Handling tree in a MySQL procedure

Question

The idea is simple - I have two tables, categories and products.

Categories:

id | parent_id | name               | count
1    NULL        Literature           6020
2    1           Interesting books    1000
3    1           Horrible books       5000
4    1           Books to burn        20
5    NULL        Motorized vehicles   1000
6    5           Cars                 999
7    5           Motorbikes           1
...

Products:

id | category_id | name
1    1             Cooking for dummies
2    3             Twilight saga
3    5             My grandpa's car
...

Now while displayed, the parent category contains all the products of all the children categories. Any category may have children categories. The count field in the table structure contains (or at least I want it to contain) count of all products displayed in this particular category. On the front-end, I select all subcategories with a simple recursive function, however I'm not so sure how to do this in a SQL procedure (yes it has to be a SQL procedure).The tables contain about a hundread categories of any kind and there are over 100 000 products.
Any ideas?

Answer 1

Bill Karwin made some nice slides about hierachical data, and the current Adjacency Model certainly as pros, but it's not very suited for this (getting a whole subtree).

For my Adjacency tables, I solve it by storing / caching the path (possibly in a script, or in a 'before update trigger'), on change of parent_id id, a new path-string is created. Your current table would look like this:

id | parent_id | path    | name               | count
1    NULL        1         Literature           6020
2    1           1:2       Interesting books    1000
3    1           1:3       Horrible books       5000
4    1           1:4       Books to burn        20
5    NULL        5         Motorized vehicles   1000
6    5           5:6       Cars                 999
7    5           5:7       Motorbikes           1

(choose any delimiter not found in the id you like)

So, now to get all products from a category + subcategories:

SELECT p.*
FROM categories c_main
JOIN categories c_subs
ON c_subs.id = c_main.id 
   OR c_subs.path LIKE CONCAT(c_main,':%')
JOIN products p
ON p.category_id = c_subs.id
WHERE c_main.id = <id>

Answer 2

Take a look at this article on managing heirachical trees in MySQL.

It explains the disadvantages to your current method and some more optimal solutions.

See especially the section towards the ended headed 'Aggregate Functions in a Nested Set'.