How to delete lots of related keys at once

[Don MacAskill]

Clint Webb wrote:
> 
> Why is everyone using memcache for the SQL query as a key?  That is what 
> the query cache on your database is for.  You gain very little doing it 
> this way, except now you have to manage the invalidation yourself, which 
> the query-cache does for you.
> 

While I have no experience with, say, PostgreSQL's or Oracle's query 
caches, MySQL's is terrible for this.

The way it invalides the cache is "if TABLE has changed in any way, get 
rid of any cache entries referencing this TABLE":

INSERT INTO table (id) VALUES (1);
SELECT * FROM table WHERE id=1;
// this query is now cached
INSERT INTO table (id) VALUES (2);
// the first SELECT is now not cached anymore, despite the 2nd insert 
having nothing to do with it

You can, of course, do much better than this yourself if you goal is to 
cache certain SQL queries.   Which is why some people do this, or 
something like it, using memcached.

There are other reasons, too, like memory size.  What if you want to 
cache, say, 1TB of queries?  Most of us don't have DB boxes with 1TB of 
RAM...  But putting together 32 boxes with 32GB each is easily doable 
(easily being relative to getting a 1TB server).

Don