The MySQL Query Cache: Why It No Longer Exists

1. What the MySQL query cache was

The MySQL query cache was an instance wide memory area that stored the complete result of a SELECT statement together with the exact text of the request. When an identical query arrived again, byte for byte identical including capitalization and whitespace, the MySQL query cache returned the stored result without troubling the optimizer, the storage engine layer, or the buffer pool again. That sounded like an elegant idea for read heavy applications with many repeated queries, such as product listings or category pages in an online shop.

The query cache was enabled through the query_cache_type variable, its allocated memory sized via query_cache_size, and query_cache_limit capped the maximum result size per cached query. At first glance the configuration looked simple: set a value, restart the server, done. In practice it quickly became clear that the MySQL query cache only paid off under very specific conditions, namely near static tables with heavy reads and almost no writes.

Even the exact text match was a practical problem. Two semantically identical queries such as SELECT * FROM products WHERE id = 5 and select * from products where id=5 counted as completely separate entries to the query cache. Even one extra whitespace character or a comment in the statement prevented a cache hit. For dynamically generated queries from ORMs, where formatting and parameter order could differ slightly, the hit rate dropped drastically below its theoretical potential.

-- These two statements are semantically identical
-- but the query cache treats them as separate cache entries
SELECT * FROM products WHERE category_id = 12 AND status = 1;
SELECT   *   FROM products WHERE category_id = 12 AND status = 1;

-- Checking cache status on a MySQL 5.7 instance
SHOW VARIABLES LIKE 'query_cache%';
-- +------------------------------+---------+
-- | Variable_name                | Value   |
-- +------------------------------+---------+
-- | query_cache_limit            | 1048576 |
-- | query_cache_min_res_unit     | 4096    |
-- | query_cache_size             | 0       |
-- | query_cache_type             | OFF     |
-- | query_cache_wlock_invalidate | OFF     |
-- +------------------------------+---------+

2. The architecture: one global lock for every request

The real design flaw of the MySQL query cache was not the idea of result caching itself, but the implementation of its access protection. Every single interaction with the cache, whether a lookup for a new query, inserting a freshly computed result, or invalidation after a write, had to acquire a single global mutex. This mutex was instance wide, not per table or per database, but one lock for the entire MySQL query cache.

On a single core server or under low concurrency this went unnoticed. But once multiple CPU cores had to process requests in parallel, the global lock became a hard serialization boundary. Even plain SELECT queries that had nothing to do with each other content wise had to share the same mutex. A connection pool with 32 concurrent threads on a 16 core server could therefore only exploit a fraction of the theoretical parallelism, because every thread waited briefly on the query cache mutex right before the actual data access.

Benchmark data from the MySQL community and from Percona's own measurements showed the effect clearly: as the number of concurrent connections rose, throughput with the query cache enabled eventually dropped below throughput with the cache completely disabled. The reason was simple, the time threads spent waiting on the mutex exceeded the time saved by avoided query executions. This exact behavior turned the MySQL query cache into an anti pattern rather than an optimization on modern multi core systems.

3. Why writes stalled the query cache

Even more severe than the lock on reads was the behavior on write operations. Every INSERT, UPDATE, or DELETE on a table triggered a complete invalidation of all cached results referencing that table, regardless of whether the change actually touched the rows of a given cached query. If a single row changed in a million row table, the MySQL query cache discarded every cache entry for that table, even if ninety nine percent of it would have remained unaffected in content.

This coarse grained invalidation at the table level meant that the query cache spiraled into a negative pattern whenever frequent writes and frequent reads hit the same table. Every write forced the cache to be dropped, every subsequent read had to recompute the query from scratch and then write it back into the cache, which again required the global lock. For a typical e-commerce database with cart updates, stock changes, and concurrent product queries, this meant in practice that the cache was emptied and refilled every second without ever being stably used.

-- A single UPDATE invalidates every cached result for this table,
-- even results that never touched the affected row
UPDATE products SET stock = stock - 1 WHERE id = 4821;

-- All of these previously cached SELECTs are now discarded,
-- regardless of whether row 4821 appeared in their result set
SELECT * FROM products WHERE category_id = 3;
SELECT * FROM products WHERE price > 50;
SELECT COUNT(*) FROM products WHERE status = 1;

4. Invalidation storms in practice

In production systems with heavy write load, a phenomenon emerged that operations teams called an invalidation storm. Once enough concurrent writes and reads met on the same heavily used tables, the mutex of the MySQL query cache turned into the actual bottleneck of the entire instance, visible in SHOW STATUS as high wait times on Query_cache_lock and a strikingly low ratio of Qcache_hits to Qcache_inserts. Instead of gaining performance, the application lost performance to a feature that was supposed to help.

The status indicator Qcache_lowmem_prunes showed another symptom, how often the cache had to evict entries due to memory pressure before they were ever used once. With fragmented cache memory, caused by the constant invalidation of large and small result sets, this value often rose in parallel with load instead of scaling with it. Experienced DBAs usually only recognized the pattern after a load test or a real peak event, such as a sale with many concurrent orders, where response times exploded instead of dropping despite an active query cache.

5. The removal in MySQL 8.0

Oracle officially marked the MySQL query cache as deprecated as early as MySQL 5.7.20 and removed it entirely with the release of MySQL 8.0.3 in 2018. The reasoning in the official changelog was unambiguous: the query cache scaled poorly on systems with high concurrency, and modern alternatives outside the database core would solve the same task more reliably and flexibly. All related variables, including query_cache_type and query_cache_size, disappeared entirely from the server.

Anyone who tries today to run SET GLOBAL query_cache_type = 1 on a MySQL 8 instance gets a clear error, because the variable simply no longer exists. For migrations from MySQL 5.7 to 8.0 this means that any my.cnf that still contains query cache parameters must be cleaned up before the upgrade, otherwise the server will not even start. This radical cut was a deliberate decision by the MySQL team to consistently move responsibility for caching strategy out of the database server and into the application layer, where it can be solved more flexibly and without a global lock.

# my.cnf: MySQL 5.7, query cache still available (but not recommended)
[mysqld]
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M

# my.cnf: MySQL 8.0, these lines cause a startup failure:
# ERROR: unknown variable 'query_cache_type=1'
# Remove all query_cache_* entries before upgrading to 8.0

6. What replaces the query cache today

Instead of an instance wide result cache inside the database server, the modern MySQL architecture relies on several specialized caching layers outside the server. First and foremost is the InnoDB buffer pool, which keeps data pages and index pages in memory and thereby avoids repeated physical reads from disk, though it operates at the page level and does not cache finished query results. For actual result caching, external systems such as Redis or Memcached today take on the role the MySQL query cache used to play, but without its global lock.

The decisive architectural difference: an external cache like Redis lives outside the database process and therefore never blocks the MySQL server itself. Invalidation happens in a targeted way through application logic, events, or time to live values, instead of blanket invalidating an entire table on every write. Applications can decide for themselves which records to cache, for how long, and at what granularity, allowing far finer control than the old, all or nothing MySQL query cache ever offered.

In addition, proxy solutions such as ProxySQL have established themselves, offering selective query caching at the connection level without burdening the actual database process. Such proxies allow specific query patterns to be cached deliberately while others are excluded, which is more practical for mixed workloads with both heavily read and heavily written tables than a blanket cache inside the server itself.

7. Building application level caching with Redis

A typical Redis based replacement pattern for the MySQL query cache follows the cache aside pattern: the application first checks Redis for a matching key, on a miss it runs the MySQL query, and then writes the result back into Redis with a TTL. The decisive advantage over the old query cache lies in targeted invalidation, instead of the entire table only the specifically affected cache key is deleted once the underlying data changes.

For product catalogs, a key scheme that encodes the relevant filter parameters directly into the cache key proves useful, for example products:category:12:page:1. If a single product changes, only the affected key or a limited set of related keys needs to be invalidated, not the entire product catalog. This targeted invalidation is exactly the mechanism the MySQL query cache structurally lacked, because it could only operate at the table level, not at the row or query level.

-- Key design for a Redis-backed replacement: encode the filter
-- parameters directly in the cache key so invalidation stays targeted
-- products:category:{category_id}:page:{page}
-- products:detail:{product_id}
-- Only the affected keys need to be dropped on write, not a whole table
SELECT id, name, price, stock FROM products
WHERE category_id = 12
ORDER BY name
LIMIT 20 OFFSET 0;

# Cache-aside pattern against Redis, replacing the old MySQL query cache
# 1. Look up the cache key first
redis-cli GET "products:category:12:page:1"

# 2. On a cache miss, run the MySQL query directly
mysql -e "SELECT id, name, price FROM products WHERE category_id = 12 LIMIT 20 OFFSET 0;"

# 3. Store the serialized result with a bounded TTL (e.g. 300 seconds)
redis-cli SETEX "products:category:12:page:1" 300 "<serialized-json-result>"

# 4. On a write to the affected product, invalidate only the related keys
redis-cli DEL "products:category:12:page:1" "products:category:12:page:2"

8. Configuring a second level cache in ORMs correctly

Many ORM frameworks now come with their own second level cache, which conceptually sits closer to the original MySQL query cache than a manually maintained Redis cache, but operates without its global lock. Doctrine for PHP, Hibernate for Java, and similar frameworks do not cache raw SQL results but hydrated entities or query results at the application level, usually backed by Redis or Memcached as a storage backend.

The decisive configuration point is the invalidation strategy, either TTL based with a fixed expiry, or event based, where the ORM automatically removes the related cache entries when an entity is saved. For data with a high change frequency, such as stock levels, a short TTL of a few seconds combined with explicit invalidation on critical write operations is recommended. For nearly static reference data such as country lists or category trees, TTLs of several hours to days are common, because the risk of stale data there is low.

9. Migration: disabling query_cache_type and measuring alternatives

When migrating from MySQL 5.7 to 8.0, the first step is to disable the query cache in production already before the actual upgrade and measure the impact. In practice it is advisable to run with query_cache_type = 0 and query_cache_size = 0 for a few days in production while comparing response times and CPU utilization to the previous state. In most cases with more than a few concurrent connections, average latency drops noticeably because the global lock is gone.

The next step is building the replacement solution, usually Redis for dynamic query results and an enabled second level cache in the ORM for entity lookups. When measuring, it is important not to look at the cache hit rate in isolation, but at overall latency under real load, because a Redis cache with a low hit rate but a fast fallback query can perform better overall than a high percentage MySQL query cache whose lock slows down parallel processing.

10. Summary

The MySQL query cache was a well intentioned idea with a structural weakness that became more painful as concurrency increased: a single global mutex for the entire instance. Every request, whether a lookup, an insert, or an invalidation, had to share this lock, which became a real bottleneck on multi core servers. Write access made the problem worse still, because every change to a table discarded all cached results for that table, regardless of whether the affected rows were even part of the cache entry.

With MySQL 8.0.3 the query cache was consistently removed, and responsibility for result caching moved to where it belongs better: the application layer. Redis as an external cache with targeted invalidation, combined with an ORM's own second level cache and a well sized InnoDB buffer pool, replaces the old MySQL query cache today more reliably, with finer granularity, and without its lock problem. Anyone who still finds query cache parameters in a production my.cnf should remove them before the next upgrade and replace them with a modern caching strategy.

11. FAQ: The MySQL Query Cache