Unlocking MySQL Query Cache: How It Works, Tuning Tips, and Real‑World Experiments

1. Workflow and Related Parameters/Commands

MySQL's query cache stores the raw result set of a SELECT statement, allowing identical queries to retrieve results directly from memory. The processing flow is:

A) Server receives the SQL and builds a hash key from SQL+DB+Query_cache_query_flags to look up the cache.

B) If a matching result set is found, it is returned to the client.

C) If the cache miss occurs, the server performs privilege checks, parsing, optimization, and execution.

D) After execution, the result set is saved into the cache.

1.1 Workflow Details

The above steps describe the complete lifecycle of a cached query.

1.2 Parameters and Commands

Key variables controlling the cache can be listed with SHOW VARIABLES LIKE '%query_cache%'. Important status variables are visible via SHOW STATUS LIKE '%Qcache%'. The main management commands are: FLUSH QUERY CACHE – cleans fragmentation without removing cached queries. RESET QUERY CACHE – clears all cached content.

2. Query Cache Strategy

2.1 Finding Results in the Cache

When a query packet arrives, MySQL checks the cache before parsing. The lookup is performed in query_cache_send_result_to_client() (defined in sql/sql_cache.h) and implemented in sql/sql_cache.cc. The hash key is built from SQL+DB+Query_cache_query_flags, with leading/trailing spaces ignored.

2.2 When Results Are Inserted

After a query produces a result set, the server stores the result packet in the cache via query_cache_insert() (located in sql/sql_cache.cc). Insertion depends on the query type and cache eligibility.

2.3 Finding a Free Block

The cache is initially a single free block. When a new query needs space, MySQL searches for the smallest free block that fits; if none exists, it selects the largest block smaller than the required size, evicts the oldest query, and retries.

2.4 Splitting and Merging Free Blocks

If a suitable block is larger than needed, it is split, ensuring the remainder is not smaller than min_allocation_unit_bytes. Adjacent free blocks are merged into a larger block whenever possible.

2.5 Free‑Block Storage

Free blocks are organized into multiple size‑ordered regions. Region 1 stores blocks ≤ query_cache_size>>4 (≈ 1/16 of the total cache). Subsequent regions store increasingly larger blocks, each region holding roughly 1.2× the number of blocks of the previous region, with the minimum size approaching min_allocation_unit_bytes.

2.6 Cache Defragmentation (FLUSH QUERY CACHE)

Fragmentation occurs as blocks are allocated and freed. FLUSH QUERY CACHE performs two operations:

Merge free blocks by moving all free blocks to the end of the cache and coalescing them into a single block.

Merge result sets by scanning cached queries and moving result data into a common block when possible.

Both operations are illustrated by the following diagrams:

3. Data Structures

3.1 Query_cache Class

The global query_cache instance of the Query_cache class represents the cache. Its main members include pointers to the cache memory, size limits, and linked lists of cache blocks.

3.4 Query and Result‑Set Blocks

A query block stores the SQL text; a result‑set block stores the actual rows. Result‑set blocks form a doubly‑linked list, allowing traversal to locate a query's results. The most recently hit queries are moved to the end of the list, implementing an LRU replacement policy.

3.5 Table List

When a table is modified, all cache blocks referencing that table (both query and result blocks) are removed via a table‑linked list, enabling fast invalidation.

4. Simple Experimental Verification

Three screenshots illustrate the cache behavior:

The first execution shows the full parsing, optimization, and result generation path. The second execution of the same SELECT skips most of those steps, confirming a cache hit. Additional traces show that DML statements (INSERT/UPDATE/DELETE) trigger free_query_internal and free_memory_block (in sql/sql_cache.cc), which purge all cache entries related to the modified table.

5. Conclusions

Query cache utilization is low because any table‑modifying operation clears all related cache entries.

The cache is allocated as a single contiguous memory region defined by query_cache_size, avoiding frequent allocation overhead.

Free blocks are kept ordered; smaller blocks are used more often for performance.

When a cache block is partially filled, remaining free space is reclaimed.

Cache replacement evicts the oldest query block when no free space is available.

Cache hit rate is calculated as Qcache_hits/(Qcache_hits+Com_select).

Only identical SQL strings (byte‑for‑byte) can hit the cache; differences in case, whitespace, or nondeterministic functions prevent caching.

Cacheable objects include only SELECT statements; SHOW commands, stored procedures, prepared statements, cursors, and queries containing volatile functions or user variables are excluded.

Modifications to a table invalidate all cached queries for that table, reducing cache effectiveness for write‑heavy workloads.

Fragmentation grows with cache size; FLUSH QUERY CACHE can defragment but may block queries for a noticeable period.

Therefore, enabling the query cache benefits read‑heavy, write‑light workloads, while disabling it may improve performance for write‑intensive applications.