Large tree structure element counters?

Question

MySQL

There is a nested tree structure of 70,000 elements - a tree of product categories. The task is to display those categories that have at least one product, and display the number of products for each category.
The question is how best to implement it. There was an attempt to update the counter of each category on mysql triggers when changing / adding / deleting a product, but when loading a large amount of goods, such a system works extremely unstable, or rather for a long time. Has anyone faced such issues?

Answer 1

Also, if we are talking about an online store, you can cache the number of products in each category, or recalculate and save the cron once every 5 minutes (via the SELECT COUNT (*) GROUP BY category_id query), but I like the L0NGMAN approach more.
> There was an attempt to update the counter of each category on mysql-triggers when changing / adding / deleting goods, but when loading a large amount of goods, such a system does not work extremely stable, or rather for a long time.
So you have bad code, you need to optimize it, maybe do the same without triggers.

Answer 2

In the table where categories are stored, I have a field items (int unsigned). When adding a product in this category, I add 1, without any triggers. Works without problems.