What nested set construction algorithms do you know?

Question

MySQL

Right off the bat, I found only one article on this topic - habrahabr.ru/post/153861
The proposed algorithm is distinguished by its phenomenal computational complexity. A little more original ( www.ibase.ru/devinfo/DBMSTrees/sqltrees.html) again does not work well, essentially emulating tree traversal.
I tried to figure out why such difficulties, and why the problem cannot be solved by means more "pleasant" for tabular SQL - but I did not find other solutions online (I do not consider options through data upload, and I do not consider processing "in normal languages").
I myself solved the problem of building in 5 queries * the depth of the tree, and I still can’t understand why no one does this ...

Answer 1

imho
Because it's all by and large entertaining puzzles for the mind.
On a live project, for trying to make requests to the database in a cycle, an evil CTO / CIO / DBA will come to you and tear off unnecessary parts of the body.
If you need to build a tree from an ordinary relational database, the correct approach is to make 1 query to the database and build the tree using the language.
If this needs to be done regularly, it might make sense to think about storing this tree in some other way.