I am using MySQL to store reports from a tool. I am extremely happy with the speed and flexibility with which users can query data. The tool also has some data which is a graph. My question is, is it a good idea to store the graph in MySQL? The number of nodes and edges in the graph is in the millions and queries are usually graph traversals.
Graph databases have advantages for use cases such as social networking, recommendation engines, and fraud detection, when you need to create relationships between data and quickly query these relationships. The following graph shows an example of a social network graph.
If the connections within the data are not the primary focus and the data is of a transactional nature, then a graph database is probably not the best fit. Sometimes it's just important to store the data and complex analysis isn't needed.
For example, one graph with each of the SUM(SELECT), SUM(INSERT), SUM(UPDATE), and SUM(DELETE) across the collection. Such as the group-level Database Activity - All MySQL Instances graph.
Plain SQL is usually unfit for manipulating a graph datastructure. There are techniques to index it, however.
For instance, if yours is not frequently updated, using a GRIPP index will let you handle graph traversal queries extremely well. The latter lets you answer parent-child and depth-related queries in more or less fixed time -- irrespective of the graph's number of nodes or density of links.
SQL databases don't handle graph data very well in general. The problem is that to do a graph traversal you either have to pull the entire graph into memory in a single query, then manipulate it and store the changes, or you have to perform huge amounts of joins to traverse the graph one node at a time, which becomes prohibitively slow. With graphs of the scale you are looking at it would probably be better to use a graph database or to use a memory database like REDIS as a fast caching layer and then persist it in the background.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With