Up until recently, I have viewed the query cache as a very important tool to improve query performance. Today, I was listening to a podcast that discussed tuning the query cache to 0, and using a better memory caching solution (such as memcache.d).
But they also mentioned that there are a few cases in which query_cache is helpful. So a general recommendation would be to have it enabled to on-demand (using SELECT SQL_CACHE, with a query_cache_type = 2 config setting).
My question is, assuming you've got a caching solution like memcache.d in place, what type of circumstances would make the query_cache more optimal?
Edit: added link
Best Answer
Memcached (or Coherence) caches entire result sets. A cache in the database caches database rows. So let's say you have an access pattern where the query is fixed and the data changes infrequently (e.g.
select * from restaurants where location='london'). You might run that query thousands of times for every time that a new restaurant is added, so caching the entire result set makes sense, it saves going to the database every time - but you still have all the manageability and flexibility of the RDBMS and SQL (you just need to kick out the cache on the odd occasion the data changes). Some people call this reference data or static data.But let's say you have an ad-hoc access pattern (perhaps there are lots of options for your user to find exactly where they want to eat tonight, but it's rare for two users to have exactly the same preferences). Then you might want to cache the rows (to save going to the disk) but assemble each result set on-the-fly in memory. That's when you would want the database itself to manage what and how it caches. In most cases, a hybrid or layered approach will work best.
Note that there is also a third kind of caching in action - the OS's filesystem cache. I don't like these, for the simple reason that if you read a block from the disk it now exists in the database cache and the filesystem cache, yet the database doesn't "know" about the latter, so it can't do anything clever with it, like see how often it is used. From the DBA's perspective, any spare memory on the system over and about what the OS itself needs to be happy is wasted.