MySQL's query cache was removed in MySQL 8.0 — and for good reason. Here is why it was problematic and what to use instead for query result caching.
Why the Query Cache Was Removed
- Global mutex on every read and write — caused severe contention at scale
- Cache invalidated on ANY write to a table, even unrelated rows
- Fragmentation grew over time, requiring periodic flushing
- Benchmark studies showed it hurt performance for most workloads
sql
-- MySQL 5.7: check if query cache is helping or hurting
SHOW STATUS LIKE 'Qcache%';
-- Qcache_hits = 0 but Qcache_inserts is high? It's wasting memory.
-- Qcache_lowmem_prunes high? Cache is too small and evicting constantly.Modern Alternative 1: Application-Level Cache (Redis)
python
import redis, json
r = redis.Redis(host='localhost', port=6379)
def get_user(user_id: int):
cache_key = f'user:{user_id}'
cached = r.get(cache_key)
if cached:
return json.loads(cached)
row = db.execute('SELECT * FROM users WHERE id = %s', (user_id,)).fetchone()
r.setex(cache_key, 300, json.dumps(row)) # TTL 5 minutes
return rowModern Alternative 2: ProxySQL Query Cache
sql
-- ProxySQL has its own query cache (works differently from MySQL QC)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, cache_ttl, apply)
VALUES (100, 1, '^SELECT .* FROM product_catalog', 60000, 1);
-- cache_ttl in milliseconds (60000 = 60 seconds)
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
-- Check cache stats
SELECT * FROM stats_mysql_query_cache;Modern Alternative 3: MySQL 8.0 Result Set Caching
In MySQL 8.0, use generated columns and covering indexes to make repeated queries use memory-resident data rather than full scans:
sql
-- Pre-aggregate frequently-read summaries
CREATE TABLE product_stats_cache (
product_id INT PRIMARY KEY,
total_sales DECIMAL(10,2),
order_count INT,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- Refresh via scheduled event or trigger
CREATE EVENT refresh_product_stats
ON SCHEDULE EVERY 5 MINUTE
DO
INSERT INTO product_stats_cache (product_id, total_sales, order_count)
SELECT product_id, SUM(amount), COUNT(*) FROM orders GROUP BY product_id
ON DUPLICATE KEY UPDATE
total_sales = VALUES(total_sales),
order_count = VALUES(order_count);Key Takeaways
- The MySQL query cache was removed in 8.0 — do not try to re-enable it with plugins
- Application-level Redis caching with TTLs is the correct modern approach for hot read paths
- ProxySQL's query cache provides transparent caching without application changes
- Pre-computed summary tables (materialized view pattern) eliminate expensive aggregations
JusDB Can Help
Replacing the MySQL query cache with a proper caching strategy is an architectural decision. JusDB can design the right caching layer for your application.