I have an MPTT tree of over 100,000 records stored in MySQL using lft
, rght
and parent_id
columns. Now the left/right values became corrupted, while the parent ids are still intact. It would require tons of queries to repair it in the application layer. Is there a good way to put the burden on the database and have it recalculate the left/right values using only SQL?
Just to clarify, I need to recalculate the numeric lft/rght values of a nested set, not the ids of neighboring records.
(source: mysql.com)