Hierarchical Queries in MySQL (Function substitute for CONNECT BY)

Re:Hierarchical Queries in MySQL (Function substitute for CO... by nbpoole on Monday April 11, 2011 @06:50PM (#2434802)

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.

[ Parent | on HN ]

Re:Hierarchical Queries in MySQL (Function substitute for CO... by quassnoi on Tuesday April 12, 2011 @01:44AM (#2436056)

Sure, especially if you nest the sets using a datatype indexable with an R-Tree index: 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.

[ Parent | on HN ]

Re:Hierarchical Queries in MySQL (Function substitute for CO... by ratsbane on Monday April 11, 2011 @07:01PM (#2434844)

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.

[ Parent | on HN ]