Perhaps it could be helpful to start to think using a graphDB together with MySQL to help all heavy search types - and also offer some other features in the navigation.
The hard part would be to stay update with the two datasets: of course the source of the data would be MySQL but for every data change we can spawn a process to create the graph underneath to be stored in the graphDB.
Which graphDB?
There are many graphDB engines out there, so the idea is to pickup one of them and use it.
The beast approach will be to adopt a Blueprint API to let us swap the DB without change the API above.
We can use a pure GraphDB (either Neo4J, InfiniteGraph or Titan) or a pure NoSQL solution with on top the Blueprint API. In the latter option I would consider either MongoDB, CouchDB or OrientDB. This last one it's very fast.
How to sync?
We could have a queue for population processes: for each group we could push a task in the group and make it work behind the scenes.
This task would run generically for the entire dataset to create the graph: the query for each search will just be sent to the graphDB then from ruby to retrieve straight away the complete result of each subset.
This will unload the MySQL system and so, also the entire server.
Perhaps it could be helpful to start to think using a graphDB together with MySQL to help all heavy search types - and also offer some other features in the navigation.
The hard part would be to stay update with the two datasets: of course the source of the data would be MySQL but for every data change we can spawn a process to create the graph underneath to be stored in the graphDB.
Which graphDB?
There are many graphDB engines out there, so the idea is to pickup one of them and use it. The beast approach will be to adopt a Blueprint API to let us swap the DB without change the API above. We can use a pure GraphDB (either Neo4J, InfiniteGraph or Titan) or a pure NoSQL solution with on top the Blueprint API. In the latter option I would consider either MongoDB, CouchDB or OrientDB. This last one it's very fast.
How to sync?
We could have a queue for population processes: for each group we could push a task in the group and make it work behind the scenes. This task would run generically for the entire dataset to create the graph: the query for each search will just be sent to the graphDB then from ruby to retrieve straight away the complete result of each subset. This will unload the MySQL system and so, also the entire server.