Relational databases are meant to store flat lists of data, yet so often I need to use single tables for hierarchical data structures, with parent/child relationships galore. There are a few different ways of doing this, and I’ve never been satisfied with the efficiency of any of them. (Two nights ago I wrote just such a piece of code for Richmond Sunlight. It took 33 seconds to execute.) Fortunately, somebody tipped me off to “Managing Hierarchical Data in MySQL,” an article by Mike Hillyer that provides a series of how-tos on the most efficient methods of writing code for working with nested sets. Now I think I’ll be able to shave a good 32.9 seconds off of that query.
Comments are closed.
interesting article; thanks!
I suppose it depends on whether you want the bulk of the work to occur during the read or the write.