Caching MySQL queries
Hi. memcached is not really good for MySQL. Then, what is a good way or practice to cache MySQL queries? Do you recommend any reading on caching?
On linux, edit my.cnf and add the following:
[mysqld]
...
query_cache_type=1
query_cache_limit=1M
query_cache_size=128M # or more - depends on you total available RAM
key_buffer=128M # or more
Once you've written fresh data to the database, is it okay for you to keep receiving stale data in response to later queries?
If you've got multiple web servers, do they all have to return the same data, or is it okay if some lag behind the others?
If you're happy to serve stale (versus some tunable time metric) data inconsistently across different servers, then just find a database client library that does client-side result set caching (ADODB for PHP will do this, either in the filesystem or against memcache) and run with it.
Memcached is perfectly fine to use with MySql, just don't cache query results directly, cache the individual rows so there's only ever one copy of that row cached. You can then update the cache when you update the db. If you need to cache queries, cache just the keys for the result of the query and use them to go back and fetch the cached rows individually from Memcached. Use a multi get for faster retrieval.
Memcached is a distributed object cache, keep each object in it only once and you won't need to expire stuff. You could also use the object version as part of the cache key which will auto expire older version by making all the clients look for the newer version. Old versions will be pushed out naturally as newer version fill up the cache.