This is a bad way to store hierarchical data in MySQL: the performance is terrible due to the number of queries that have to be issued. The nested set model (http://en.wikipedia.org/wiki/Nested_set_model) is a better solution.
Good point, but using nested sets means a lot of work when you update. Doing the work in a UDF is a lot quicker than putting it into the code that calls the SQL. I've done that. [sigh] Maybe I shouldn't admit to it.
However, nested sets is still very expensive to update which may be a deciding factor if your hierarchy is subject to frequent change (like, a folder-like structure with the folders being moved here and there) and you don't need to query for "all descendants" often.
3 comments
[ 2.9 ms ] story [ 15.2 ms ] threadHowever, nested sets is still very expensive to update which may be a deciding factor if your hierarchy is subject to frequent change (like, a folder-like structure with the folders being moved here and there) and you don't need to query for "all descendants" often.