Before diving into a deep performance investigation — wait stats, execution plans, I/O benchmarks — it’s worth checking whether a simple configuration oversight is behind the problem. One of the most common and highest-impact quick wins in MySQL is query cache sizing. A cache that’s disabled, too small, or incorrectly sized can account for a significant portion of query latency on read-heavy workloads.

Note: MySQL’s query cache was deprecated in MySQL 5.7.20 and removed entirely in MySQL 8.0. If you’re running MySQL 8.0+, this article doesn’t apply — consider ProxySQL or application-level caching instead.

How MySQL Query Caching Works

When query caching is enabled, MySQL stores the result of a SELECT query in memory. Subsequent identical queries (same text, same schema context) are served directly from memory without hitting disk. A cached result is invalidated as soon as any data in the underlying tables changes.

Three parameters control the behavior:

• query_cache_size — total memory allocated to the cache. Default: 0 (disabled).
• query_cache_limit — maximum size of a single cached result. Results larger than this value are not cached.
• query_cache_min_res_unit — minimum allocation block size. Smaller values reduce memory fragmentation for small results.

Step 1: Check Current Configuration

SHOW VARIABLES LIKE '%query_cache%';
-- Expected output on an enabled instance:
-- +------------------------------+----------+
-- | Variable_name                | Value    |
-- +------------------------------+----------+
-- | have_query_cache             | YES      |
-- | query_cache_limit            | 2097152  |  -- 2 MB max per result
-- | query_cache_min_res_unit     | 4096     |  -- 4 KB blocks
-- | query_cache_size             | 33554432 |  -- 32 MB total
-- | query_cache_type             | ON       |
-- +------------------------------+----------+

Step 2: Analyze Cache Effectiveness

After the instance has been running under normal workload for at least a few days, check the cache performance metrics:

SHOW STATUS LIKE 'QCache%';

Key metrics to evaluate:

• Qcache_hits / Qcache_inserts ratio: a high ratio means the cache is effective. A ratio below 1:1 suggests most queries aren’t benefiting.
• Qcache_lowmem_prunes: entries evicted due to memory pressure. A high and growing value means query_cache_size is too small.
• Qcache_not_cached: queries that bypass the cache (non-cacheable queries like those with NOW(), RAND(), or non-deterministic functions).
• Qcache_free_memory: remaining free space. If this is consistently near zero, increase query_cache_size.

Recommended Starting Configuration

There’s no universal “best” configuration — tuning depends on your workload pattern, result set sizes, and write frequency. As a starting point:

• Enable the cache with a modest size (32–128 MB) and monitor for a week before tuning further.
• Keep query_cache_min_res_unit small (4096 bytes) to reduce fragmentation.
• On write-heavy workloads, the cache can actually hurt performance due to constant invalidation overhead — consider disabling it entirely in that case.

For the full parameter reference: MySQL 5.6 Query Cache documentation.