Recursive update for the parent rows in MySQL

CREATE TABLE record (
  id INT PRIMARY KEY,
  parent_id INT,
  count INT NOT NULL
)

I have a table defined as above. The field 'parent_id' refers to the parent of the row, so the whole data looks like n-ary tree.

According to the business logic I have, when the field 'count' of a row is requested to increment (by one, for example), all of the ancestor nodes (or rows) should be updated to increment the 'count' field by one as well.

Since this 'count' field is expected to updated frequently (say 1000/sec), I believe that this recursive update would slow down the entire system a lot due to a huge cascading 开发者_JS百科write operation in DBMS.

For now, I think a stored procedure is the best option I can choose. If MySQL support the operation like 'connected by' of Oracle, there can be some way to be tricky, but it doesn't, obviously.

Is there any efficient way to implement this?

Thanks in advance.

When you use stored procedures, you will still need recursion. You only move the recursion from the source code to the database.

You can use nested sets to store hierarchical data. Basically, you create two additional fields left and right where left < right. Then a node e1 is subordinate of node e2 iff e1.left > e2.left && e1.right < e2.right.

This gets rid of recursions at the price of higher costs for insertion, deletion and relocation of nodes. On the other hand, updates of node content like the one you described can be done in a single query. This is efficient, because an index can be used to retrieve a node and all of it's ancestors in a single query.