How to delete lots of related keys at once
don at smugmug.com
Fri Aug 3 02:44:36 UTC 2007
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).
More information about the memcached