When to use MySQL query_cache?

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

I think there's a lot of wrong information about the query cache out there.

The best case for the query cache, is when you have to examine a very large number of rows, but only return a few to a client. A typical situation where this is to be common, is a system where no proper optimization or indexing has been applied.

In a situation where many of the queries are primary key lookups, or otherwise very well optimized, the query cache can cause negative scalability. Yes: it makes things worse!

The reason for this, is that the design adds some internal locking, which limits your MySQL server from scaling on multi-core machines.

The query cache is a cause for many "sudden stalls" in MySQL - not all of them obvious. In Percona Server, we added a new state to the processlist (Waiting on Qcache mutex):
http://www.percona.com/docs/wiki/percona-server:features:status_wait_query_cache_mutex

(Disclaimer, I work for Percona.)

StackExchange Database Administrators Q#1705, answer score: 6