recursion - Recursive update for the parent rows in MySQL -

create table record (   id int primary key,   parent_id int,   count int not null ) 

i have table defined above. field 'parent_id' refers parent of row, whole data looks n-ary tree.

according business logic have, when field 'count' of row requested increment (by one, example), of ancestor nodes (or rows) should updated increment 'count' field 1 well.

since 'count' field expected updated (say 1000/sec), believe recursive update slow down entire system lot due huge cascading write operation in dbms.

for now, think stored procedure best option can choose. if mysql support operation 'connected by' of oracle, there can way tricky, doesn't, obviously.

is there efficient way implement this?

thanks in advance.

when use stored procedures, still need recursion. move recursion source code database.

you can use nested sets store hierarchical data. basically, create 2 additional fields left , right left < right. node e1 subordinate of node e2 iff e1.left > e2.left && e1.right < e2.right.

this gets rid of recursions @ price of higher costs insertion, deletion , relocation of nodes. on other hand, updates of node content 1 described can done in single query. efficient, because index can used retrieve node , of it's ancestors in single query.