How do you mix SQL DB vs. Key-Value store (i.e. Redis)

Question

I'm reviewing my code and realize I spend a tremendous amount of time

1. taking rows from a database,
2. formatting as XML,
3. AJAX GET to browser, and then
4. converting back into a hashed javascript object as my local datastore.

On updates, I have to reverse the process (except using POST instead of XML.)

Having just started looking at Redis, I'm thinking I can save a tremendous amount of time keeping the objects in a key-value store on the server and just using JSON to transfer directly to JS client. But my feeble mind can't anticipate what I'm giving up by leaving a SQL DB (i.e. I'm scared to give up the GROUP BY/HAVING queries)

For my data, I have:

• many-many relationships, i.e. obj-tags, obj-groups, etc.
• query objects by a combination of such, i.e. WHERE tag IN ('a', 'b','c') AND group in ('x','y')
• self joins, i.e. ALL the tags for each object WHERE tag='a' (sql group_concat())
• a lot of outer joins, i.e. OUTER JOIN rating ON o.id = rating.obj_id
• and feeds, which seem to be a strong point in REDIS

How do you successfully mix key-value & SQL DBs?

For example, is practical to join a large list of obj.Ids from a REDIS set with SQL data using a SQL RANGE query (i.e. WHERE obj.id IN (1,4,6,7,8,34,876,9879,567,345, ...), or vice versa?

ideas/suggestions welcome.

Answer 1

You may want to take a look at MongoDB. It works with JSON style objects, and comes with SQL like indexing & querying. Redis is more suitable for storing data structures likes lists & sets, when you want a simple lookup instead of a complex query.