Understanding MySQL Query Cache

MySQL Query Cache was a feature available in versions prior to 8.0 that stored the results of SELECT queries in memory. For small-scale applications where external caching solutions like Redis or Memcached aren't implemented, and where query results remain relatively stable, enabling query cache in MySQL 5.7 or earlier versions can provide performance benefits.

How Query Cache Operates

The query cache mechanism works by:

• Storing result sets from SELECT statements and prepared queries
• Comparing new queries against cached queries using exact string matching (case-sensitive)
• Returning cached results when identical queries are detected

Query Types That Cannot Be Cached

SELECT SQL_NO_CACHE ...;
Queries containing non-deterministic functions: NOW(), RAND(), UUID()
Queries targeting system databases: mysql, information_schema
Statements using session variables or stored procedure local variables
Queries with LOCK IN SHARE MODE or FOR UPDATE clauses
SELECT ... INTO statements exporting data
Queries under Serializable transaction isolation level
Operations on temporary tables
Queries generating warnings
Statements not referencing any table or view
Queries where user has only column-level privileges

Advantages and Limitations

Benefits:

• Avoids SQL parsing and execution overhead for cached queries
• Direct result retrieval from memory

Drawbacks:

• Rigid matching criteria reduces cache effectiveness
• Additional overhead for cache maintenance and validation
• Global lock contention for cached tables

Configuration Parameters

query_cache_type:

• 0 - Disabled
• 1 - Enabled for all compatible queries
• 2 - Enabled only for queries with SQL_CACHE hint

query_cache_size: Minimum 40KB, recommended 64MB for most deployments. Larger sizes don't necessarily improve hit ratios.

query_cache_limit: Maximum result set size for caching (default 1MB). Adjust between 16KB-1MB based on application patterns.

query_cache_min_res_unit: Memory allocation unit size (default 4KB). Set between 1KB-16KB to balance fragmentation and allocation frequency.

query_cache_wlock_invalidate: Determines if queries can read cached data during write locks (default 0 - allowed).

Maintenance Operations

Defragmentation:

FLUSH QUERY CACHE;

Cache Reset:

RESET QUERY CACHE;
FLUSH TABLES;

Performance Monitoring Metrics

Fragmentation Ratio: Qcache_free_blocks / Qcache_total_blocks × 100%

Hit Rate: (Qcache_hits - Qcache_inserts) / Qcache_hits × 100%

Memory Utilization: (query_cache_size - Qcache_free_memory) / query_cache_size × 100%

Qcache_lowmem_prunes: Count of cache evictions due to memory pressure (LRU algorithm)

Optimal memory block size calculation:

optimal_block_size = (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache

Ideal Usage Scenarios

Query cache performs best in:

• Read-heavy applications: content portals, news sites, reporting systems, forums
• Tables with infrequent data modifications
• Environments with repeated identical queries
• query_cache_type=2 configuration with explicit SQL_CACHE hints

Consider disabling query cache when:

• Hit rates consistently fall below 40%
• High DML activity causes frequent cache invalidation
• Memory fragmentation becomes problematic
• Lock contention impacts performance