[MySQL] The query cache blues

The MySQL query cache is one of the prominent features in MySQL and a vital part of query optimization. It caches the select query along with the result set, which enables the identical selects to execute faster as the data fetches from the in memory.

So in theory its rocks. Right ?

Yep but not quite. First there is the obvious problem of changing query/data. If you frequently update the table then you invalid the query cache. If you change any parameters into the query you just don’t use the cache. In both case you’re probably not going to get any sort of good usage from the MySQL query cache.

Then there is also the problem of how the query cache implementation work on modern multi-core CPU. Simply stated mysqld wants to lock the query cache both when checking if a result is in the cache and when writing a result set into the cache. When writing locking can occur several times, because cache has to be assigned memory block by block. On a highly concurrent environment that means a lot of mutex which may become a performance bottleneck.

For these reason, the query cache is disabled since MySQL 5.6
If you really want/need to re-enable it, just set a value for the query_cache_limit.

Further Reading and sources