26 Feb 2003
(updated 26 Feb 2003 at 17:23 UTC) »
: After reading the articles, the method i'm using is more or less a materialized path, only using colons instead of periods. It works great, as i've found and the article indicates for fetching full trees of children and parents , but as usual, the problem is ordering.
While the Netsted set, imho seems ideal, in this particular case i'm bound to the Stock MySQL 3.23 with MyISAM tables. I see the nested sets method as only being effective with stored procedures, transactions and table locking.
I managed to come up with a way to have MySQL return a sorted result set, but it requires first knowing how deep a thread goes (for successive concatinating) and the greatest number of digits in an identifier (for padding zeros). That can all be accomplished by storing metadata in additional columns and the process can be sped up by using an intermediate table of cached ordered threads.
The materialized path method will work even if i don't get an ordered set from MySQL, i can live with sorting the set in userland but naturally, would prefer to have MySQL do it. It's just a matter of testing to see what works best.
Thanks for the advice guys.