Count number of children in limited depth tree

I have a tree structure 开发者_如何学Cin MySQL using a parent_id field for each node. The structure is quite large but only about 8 levels deep. Each node also has a child_counter field.

I'm looking for a performant way to calculate (and update) the number of children each node has (by children I am including children of children etc.), preferably with not too many SQL calls. I don't expect this to be super fast, just good enough.

I'm hoping there might be some way to do a mass update as the algorithm iterates.

I've implemented something like this before, but not quite. Are you able to store a "depth" integer on each node, to mark their depth in the tree? If so, you could do this with 8 queries, which if properly indexed would be quite reasonable. Something like this I think (don't have mysql handy). You would query from the lowest depth (i.e. 8) upwards: UPDATE Node SET child_counter = ((SELECT SUM(child_counter) FROM Node WHERE parent_id = id) + (SELECT COUNT(0) FROM Node WHERE parent_id = id)) WHERE depth = x

Here is a good article: Managing Hierarchical Data in MySQL

You're currently using an Adjacency List Model with additional count column. But actually what you try to get from hierarchical data looks like it could be modeled much easier with the Nested Set Model.