rebuild_tree performance issue - Takes too much time

[akhil018]

The rebuild_tree operation in SQLAlchemy-MPTT is experiencing performance problems and taking an unexpectedly long time to complete, particularly when dealing with large datasets. This issue significantly impacts the usability and efficiency of the library.

Expected Behavior: The rebuild_treeoperation should complete within a reasonable time frame, even as the size of the dataset grows.

Actual Behavior: The rebuild_tree operation is taking an unreasonably long time, especially noticeable when dealing with larger datasets, adversely affecting the overall performance of the library.

[evgenybf]

@akhil018 You didn't specify the size of the dataset when it starts to be unreasonably slow. I think it would be useful.

Note, that there is no magic here: Roughly,, on insert you have to update all parent nodes and the nodes whose id is larger than the id of the new node. In the worst case, it can be all records in the table.

There is good article on how it works: https://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

LOCK TABLE nested_category WRITE;

SELECT @myRight := rgt FROM nested_category
WHERE name = 'TELEVISIONS';

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;

INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2);

UNLOCK TABLES;

rebuld_tree() may take even longer if the tree is not allowed to be loaded to the memory.