MySQL hierarchical data storage

Question

I am creating a web application that must have a high write load and thousands, even millions of hierarchical records, representing user-defined / constructed trees. I am not trying to create a forum with threads, but a huge database with thousands of small hierarchies (trees with up to 10-20 descendants) ...

I know many models for storing hierarchies - I am currently using Nested Sets, but performance with huge data and load is a problem. I also doubt that Adjacency lists or something similar can solve this problem.

I experimented with the Mongo base, which is a super-fast keystore / value store, but I can only use MySQL.

I would like to hear about other people who face similar problems.

Answer 1

If you can install MySQL plugins, then OQGraph is what you need.

Answer 2

What is the problem with nested sets?

Calculates lft / rgt values ​​when adding / removing nodes?

Pretty confident with a little careful planning, you can set it up, so you only need to make rare recommendations. I did not complete this, but did some planning for the system once (the client does not need the system at the end!)

One, multiplies the values, say 1000, the first time they are calculated. Then, if you add node, you can simply insert numbers between the values. Its only when there are a large number of inserts, you begin to finish the numbers. Low priority batch process, can resell the tree to free up numbers for fresh inserts.

Deletion can also be archived using numbers. In fact, a node without children is easy. There was no recount. It turns out more difficult if the children, but I think it is doable.