Glad to hear that rebuilding the nested set solved your issues!
Regarding AtoM, databases, and nested sets:
First, some of AtoM's choice to use a relational database comes from the project history - AtoM's 1.0 beta release was in 2008, when robust open source graph databases were in their infancy - perhaps even embryonic at that point. Given that our business model makes it challenging to find funding for large scale maintenance work (and fully switching from a relational database to a graph would be a large project), this is something we have not pursued, as it would certainly require community support for us to take on.
That said, we have certainly been investigating options such as this when considering AtoM's long-term future. It's important to note that there are many things that relational databases are still better at - and many large scale projects are still using a relational database as the primary data store, while serializing to a sidecar graph db to support graph-like queries and other things more suited to that data structure. It's not inconceivable that AtoM may take this approach in the future to be able to provide linked data support. I think we'd need to see a strong case for the overall advantages to consider using only a graph database.
It's also important to note that I'm an archivist, not a developer - so any errors in the above assertions are mine!
Finally, I'll add that we ARE moving away from relying on a nested set model wherever it is advantageous to do so in the upcoming 2.6 release. We have upgraded AtoM to MySQL v8, which now supports Common Table Expressions (CTE
) as an alternative method. We have a lot of detailed analysis about the performance improvements this is providing AtoM with in the following ticket:
... and we also have several other open tickets (such as #13240
for example) we hope to implement before the 2.6 release.