What's the faster way to get a tree in MySQL?

Question

MySQL

I implemented the procedure for obtaining a tree based on depth without using recursion. It seems to me that using recursion can be implemented more optimally? In MySQL, you need to change the max_sp_recursion_depth limit in this case... I thought maybe the recursion is not in vain limited) And you can't do without explicit creation of temporary tables.

Answer 1

If I were you, I would use nested sets to store the tree ( example , another one )
Then your query would look like this:

SELECT * FROM users 
WHERE left_key >= $parent_left_key 
   AND right_key <= $parent_right_key 
   AND level <= $level 
ORDER BY left_key

Answer 2

In my project, I still changed the original idea https://gist.github.com/kissarat/3a2a88c38568102ffb50 , there is no depth parameter here