4 ms·
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 (ht
by nbpoole 16y ago
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 http://en.wikipedia.org/wiki/Nested_set_model) is a better solution.
- ratsbane 16y agoGood 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.
- quassnoi 16y agoSure, especially if you nest the sets using a datatype indexable with an R-Tree index: http://explainextended.com/2009/09/29/adjacency-list-vs-nested-sets-mysql/ http://explainextended.com/2009/09/29/adjacency-list-vs-nest... 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.