4 ms·
A great technique I've used for many years to store hierarchical data is by using a Nested Set Model (https://en.wikipedia.org/wiki/Nested_set_model https://en.
by martin-adams 11y ago
A great technique I've used for many years to store hierarchical data is by using a Nested Set Model (https://en.wikipedia.org/wiki/Nested_set_model https://en.wikipedia.org/wiki/Nested_set_model)
It works a treat as you can query the whole tree in one SQL statement but preserve the nesting for formatting.
- Kenji 11y agoThank you sir for pointing that out. This looks very interesting and I might even implement that in my own blog at some point in time. I thought about doing something similar to that (without actually knowing this technique) but I shyed away precisely because of the price you pay at insertion time. EDIT: I've been thinking some more about this. Another possibility would be to limit the depth of the tree to, say, 8 (which should be reasonable) and then make 8 fields, one for each ancestor (parent, grandparent, and so on). Changing the tree will become a nightmare but all queries for subtrees will be blazingly fast.
- MichaelGG 11y agoSince individual comment threads are never that big, just store the root parent ID and query on that. Then reconstruct in code.
- Kenji 11y agoYeah that's how I currently implemented it on my site, but it can't harm to overthink the solution of performance problems I don't (yet?) have ;)
- krapp 11y agoYou could probably solve more nonexistent problems with caching than by limiting thread depth.
- deleted 11y ago[deleted]
- krapp 11y agoTo clarify - more users are going to read and refresh pages than actually post, so making certain not every GET request results in a new database query would probably improve performance more than trying to limit the number of rows in each query. Query performance obviously matters, but with a HN like site, it's probably not going to be so critical that limiting the depth of threads is even worth the effort.