yo,
on this page What are the options for storing hierarchical data in a relational database? there is an awesome comparison of options for storing hierarchies. For me it looks like nested set is the best, but there it mentions "Requires a specific sort order". Can anybody explain what exactly does that imply? Will I be able to get a sorted list of ascendants/descendants fast only I sort them by id? Even if I have indexes on other columns that I'm trying to use for sort?
In the nested set model, the tree structure is implied by the order in which the nodes are traversed.
If you want a sort order different from that for which the tree was created, you have to read the results first, and then sort them manually in a second step. (After rows have been read from a table, indexes are no longer useful.)