MySQL Query Cache: Configuration and Limitations

Key Insights

• MySQL Query Cache was deprecated in MySQL 5.7.20 and removed entirely in MySQL 8.0 due to scalability issues and lock contention in multi-core environments • Query Cache worked by storing SELECT statement results in memory using the full SQL text as a key, but invalidated entire table caches on any write operation • Modern alternatives like ProxySQL, Redis, or application-level caching provide better performance and more granular control than the legacy Query Cache

Understanding MySQL Query Cache Architecture

The MySQL Query Cache operated as a server-level caching mechanism that stored the text of SELECT statements alongside their result sets. When an identical query arrived, MySQL would return cached results instead of executing the query again.

The cache used a hash table structure where the key was the complete SQL statement text, including whitespace and case. This meant SELECT * FROM users and select * from users were treated as different queries.

-- These queries would create separate cache entries
SELECT id, name FROM products WHERE category = 'electronics';
SELECT id, name FROM products WHERE category='electronics';  -- No space
select id, name FROM products WHERE category = 'electronics'; -- Different case

The cache operated with a single global mutex lock, creating a bottleneck as concurrent queries competed for cache access. This architectural decision made it fundamentally incompatible with modern multi-core processors.

Configuration Parameters

For MySQL versions prior to 8.0, Query Cache configuration required several parameters:

# my.cnf or my.ini configuration
[mysqld]
query_cache_type = 1
query_cache_size = 268435456  # 256MB
query_cache_limit = 2097152   # 2MB per query
query_cache_min_res_unit = 4096  # 4KB allocation blocks

The query_cache_type parameter accepted three values:

-- 0 or OFF: Completely disabled
SET GLOBAL query_cache_type = 0;

-- 1 or ON: Cache all SELECT queries except those with SQL_NO_CACHE
SET GLOBAL query_cache_type = 1;

-- 2 or DEMAND: Only cache queries with SQL_CACHE hint
SET GLOBAL query_cache_type = 2;

Using DEMAND mode provided selective caching:

-- This query will be cached when query_cache_type = DEMAND
SELECT SQL_CACHE id, email FROM users WHERE status = 'active';

-- This query will not be cached
SELECT id, email FROM users WHERE status = 'active';

Monitoring Cache Performance

MySQL provided several status variables to monitor Query Cache effectiveness:

SHOW STATUS LIKE 'Qcache%';

Key metrics included:

-- Check cache hit ratio
SELECT 
    VARIABLE_VALUE as Qcache_hits 
FROM performance_schema.global_status 
WHERE VARIABLE_NAME = 'Qcache_hits';

SELECT 
    VARIABLE_VALUE as Com_select 
FROM performance_schema.global_status 
WHERE VARIABLE_NAME = 'Com_select';

-- Calculate hit rate
SELECT 
    (qh.hits / (qh.hits + cs.selects)) * 100 as hit_rate_percent
FROM 
    (SELECT VARIABLE_VALUE as hits 
     FROM performance_schema.global_status 
     WHERE VARIABLE_NAME = 'Qcache_hits') qh,
    (SELECT VARIABLE_VALUE as selects 
     FROM performance_schema.global_status 
     WHERE VARIABLE_NAME = 'Com_select') cs;

Critical performance indicators:

-- Fragmentation check
SHOW STATUS LIKE 'Qcache_free_blocks';
SHOW STATUS LIKE 'Qcache_total_blocks';

-- Memory utilization
SHOW STATUS LIKE 'Qcache_free_memory';

-- Prune operations (cache evictions due to size)
SHOW STATUS LIKE 'Qcache_lowmem_prunes';

Cache Invalidation Problems

The Query Cache’s most significant limitation was its aggressive invalidation strategy. Any write operation (INSERT, UPDATE, DELETE) to a table invalidated all cached queries referencing that table:

-- These cached queries exist
SELECT * FROM orders WHERE status = 'pending';
SELECT COUNT(*) FROM orders WHERE created_at > '2024-01-01';
SELECT o.*, c.name FROM orders o JOIN customers c ON o.customer_id = c.id;

-- This single update invalidates ALL cached queries touching orders table
UPDATE orders SET status = 'shipped' WHERE id = 12345;

This made Query Cache counterproductive for write-heavy workloads:

-- Scenario: High-frequency updates
-- Cache constantly invalidated, creating overhead without benefit

-- Writer thread
UPDATE user_sessions SET last_activity = NOW() WHERE session_id = 'abc123';

-- Reader threads (cache misses due to constant invalidation)
SELECT * FROM user_sessions WHERE user_id = 100;
SELECT COUNT(*) FROM user_sessions WHERE last_activity > DATE_SUB(NOW(), INTERVAL 15 MINUTE);

Defragmentation and Maintenance

Cache fragmentation occurred as queries of varying sizes were cached and evicted:

-- Defragment the query cache
FLUSH QUERY CACHE;

-- Completely clear the cache
RESET QUERY CACHE;

-- Check fragmentation level
SELECT 
    VARIABLE_VALUE / (1024 * 1024) as free_memory_mb
FROM performance_schema.global_status 
WHERE VARIABLE_NAME = 'Qcache_free_memory';

SELECT 
    VARIABLE_VALUE as free_blocks
FROM performance_schema.global_status 
WHERE VARIABLE_NAME = 'Qcache_free_blocks';

The query_cache_min_res_unit parameter controlled allocation block size. Smaller values reduced waste but increased fragmentation:

-- Default 4KB blocks may waste memory for small result sets
-- Tune based on average result size
SET GLOBAL query_cache_min_res_unit = 2048;  -- 2KB blocks

Modern Alternatives

ProxySQL Query Caching

ProxySQL offers superior caching with query rule-based control:

-- ProxySQL admin interface
INSERT INTO mysql_query_rules (
    rule_id,
    active,
    match_pattern,
    cache_ttl,
    apply
) VALUES (
    10,
    1,
    '^SELECT.*FROM products WHERE category',
    60000,  -- 60 seconds
    1
);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Application-Level Caching with Redis

import mysql.connector
import redis
import json
import hashlib

class QueryCache:
    def __init__(self, redis_client, ttl=300):
        self.redis = redis_client
        self.ttl = ttl
    
    def cache_key(self, query, params):
        key_string = f"{query}:{json.dumps(params, sort_keys=True)}"
        return f"qcache:{hashlib.md5(key_string.encode()).hexdigest()}"
    
    def execute_cached(self, cursor, query, params=None):
        cache_key = self.cache_key(query, params or [])
        
        # Check cache
        cached = self.redis.get(cache_key)
        if cached:
            return json.loads(cached)
        
        # Execute query
        cursor.execute(query, params or [])
        results = cursor.fetchall()
        
        # Store in cache
        self.redis.setex(
            cache_key,
            self.ttl,
            json.dumps(results, default=str)
        )
        
        return results

# Usage
redis_client = redis.Redis(host='localhost', port=6379, db=0)
cache = QueryCache(redis_client, ttl=300)

db = mysql.connector.connect(host='localhost', user='root', database='myapp')
cursor = db.cursor()

results = cache.execute_cached(
    cursor,
    "SELECT id, name FROM products WHERE category = %s",
    ['electronics']
)

Result Set Caching in MySQL 8.0+

MySQL 8.0 removed Query Cache but improved the optimizer and introduced better alternatives:

-- Use materialized CTEs for repeated subquery results
WITH active_users AS (
    SELECT id, name, email 
    FROM users 
    WHERE status = 'active'
)
SELECT au.*, COUNT(o.id) as order_count
FROM active_users au
LEFT JOIN orders o ON au.id = o.user_id
GROUP BY au.id;

The MySQL Query Cache represented an early attempt at transparent query optimization that ultimately failed to scale with modern hardware and workload patterns. Its removal from MySQL 8.0 pushed responsibility for caching to more appropriate layers where invalidation logic can match application semantics rather than relying on blunt table-level invalidation.