Re: MySQL (all versions) and Query Cache

[Rick James]

True. The QC is a waste of time on tables that are frequently modified. I changed the Subject since this discussion applies to all versions of MySQL since the QC was added more than a decade ago.

There are other restrictions on the usefulness of the QC.

The QC has an overhead penalty of 11% on simple queries. (SQL_NO_CACHE is not sufficient to avoid the penalty.)

My general advice is to turn the Query cache totally off (unless you have a compelling reason to use it). Configure both of these:
* query_cache_size = 0
* query_cache_type = OFF

If you think you have a system where it is useful, then my advice includes:
* query_cache_size = -- no more than 50M (since a linear algorithm is used)
* query_cache_type = DEMAND
* SQL_CACHE and SQL_NO_CACHE on all SELECTs -- That is, take control of what _might_ be cached, and don't clutter the cache with resultsets that are unlikely to never be repeated.
After some time, SHOW GLOBAL STATUS LIKE 'Qc%' and SHOW GLOBAL STATUS LIKE 'Uptime'; then let's discuss whether the numbers tell you about the usefulness of the QC.

The complete purging of the table happens on Slaves, too. So, this discussion applies to replication setups.