4 ms·
> > We can't support nested groups with MySQL in a performant way > All they had to do was implement a nested set pattern for their groups [1] Nested sets com
by perlgeek 7y ago
> > We can't support nested groups with MySQL in a performant way
> All they had to do was implement a nested set pattern for their groups [1]
Nested sets come with significant downsides. They are a hassle to implement, but more importantly, a single insert can require writes to lots of rows. This is both problem for concurrent transactions and for row-based replication.
- mst 7y agoI've always found materialized path does me proud for this sort of use case. Also nicely easy to back-populate after you add the column using a CTE, if you didn't have it originally.
- dspillett 7y agoCached paths is usually pretty cheap for adding new nodes, but it can be quite expensive if you need to be able to move non-leaf nodes around (caveat: I've not looked at their use-cases here, I don't know if moving non-leaf nodes is an important matter) especially if the forest is deep rather than (or as well as) wide.