Implementation for query caching

Question

I need to implement cache for SQL queries. Say we have "SELECT id,aa,bb FROM table1 WHERE aa>1 and bb=12;" -- we want it to be cached. Also, it may be "SELECT id,aa,bb FROM table1 WHERE aa>25 and bb=11;" And can be "SELECT id,aa,bb FROM table2;" of course.

Not a big deal -- what really is a question is how to expire cache values better. On updates. Say, we added new row with "INSERT .." or updated existing data with "UPDATE ...". We need to expire all cache which had SELECTs values, as they may not be accurate any more.

How to do this better?

In the simplest case -- we use the whole "SQL query" ask key in the cache. But at the moment of INSERT/UPDATE we have no idea which queries to expire (which keys?)

Answer 1

One thing I've done before is to make a list of cache keys and store that in the cache as well.

When I need to cache something I grab the list, add the key to it, and store it back in the cache. Then when I need to clear the cache for a particular set, I grab the list and remove every key in the list from the cache and remove the list from the cache.

I get the impression this is how some cache libraries for example in NHibernate work too, but I'm not entirely sure about that.

Answer 2

This is why the general recommendation is to not attempt to use memcached as a query cache.

You've got some kind of model that is represented on the lower end by a database and the upper end by an API. You should cache near that API.

For example, don't think of SELECT id,aa,bb FROM table1 WHERE aa>1 and bb=12; as an operation, but think of the operations more as follows:

bbObject = BBObject.get(12); # reusable and independently cached BB instance.
bbObject.getAAs()            # Another reusable and independently cacheable obj

With this, you have structure to work with and it should be more obvious how to use this.

For example, whatever table1 represents here (referring to it as aa as that's what I'm able to infer from your example).

# save a new aa in `table1` and invalidate the `bbObject.getAAs` cache(s)
aa AA.new(bbObject, 5).save()