The query cache stores the result set of a SELECT query and returns the cached result directly on a cache hit, bypassing query parsing and execution entirely. This reduces CPU utilization, reduces IOPS usage in specific cases, and shortens response time while increasing system throughput for repeated identical queries.
The query cache is not universally beneficial. For write-heavy workloads or queries that rarely repeat, the cache overhead can reduce overall performance. Test before enabling in production.
When to use it
The query cache works best when:
Table data is static or updated infrequently
The same SELECT statement runs frequently
The query result set is smaller than 1 MB
How it works
Each SELECT query is hashed when it arrives at the RDS instance. The hash is used to look up the query cache:
Cache hit: The cached result set is returned to the client immediately. The query is not parsed or executed.
Cache miss: The query runs normally, and the result set is stored in the cache along with its hash for future use.
Table modification: When a table changes, all cached result sets that reference that table are invalidated and removed from the cache. This table-level invalidation is the main architectural constraint — a single write invalidates all cached queries for that table, even if their results have not changed.
Queries that are not cached
The following queries bypass the cache regardless of parameter settings:
Queries that differ in letter case, database, protocol version, or character set are treated as different queries. Each is cached separately.
Subquery result sets are not cached. Only the top-level result set is cached.
Queries that run inside a stored function, stored procedure, trigger, or event.
Queries that call
now(),curdate(),last_insert_id(), orrand(). These functions return non-deterministic values, so caching would produce incorrect results on subsequent calls.Queries that reference tables in the
mysql,information_schema, orperformance_schemadatabase.Queries that reference temporary tables.
Queries that generate warnings.
Queries using
SELECT … LOCK IN SHARE MODE,SELECT … FOR UPDATE, orSELECT * FROM … WHERE AUTOINCREMENT_COL IS NULL.Queries that reference a user-defined variable.
Queries that include the
SQL_NO_CACHEhint.
Configure the parameters
Modify these parameters in the ApsaraDB RDS console:
| Parameter | Description | Default |
|---|---|---|
query_cache_type | Controls caching behavior. 0: disabled. 1: enabled (queries with SELECT SQL_NO_CACHE are excluded). 2: disables the feature; only queries with SELECT SQL_CACHE are cached. | — |
query_cache_size | Total memory allocated to the query cache, in bytes. Set to 0 to disable. Must be a multiple of 1024. | 3 MB |
query_cache_limit | Maximum result set size for a single cached query, in bytes. Result sets larger than this value are not cached. | 1 MB |
Enable condition: query_cache_size > 0 AND query_cache_type = 1 or 2
Disable condition: query_cache_size = 0 OR query_cache_type = 0
Changing query_cache_type restarts the RDS instance automatically. Set query_cache_size to a multiple of 1024, or the console returns a "the specified parameter is invalid" error. 
Sizing guidance
Start with a value between 10 MB and 100 MB for query_cache_size, based on your instance type, and tune from there. A large cache consumes memory that other processes need and increases the overhead of maintaining cache consistency on every write.
Verify the cache is working
Run the following statement to check cache activity:
show global status like 'Qca%';
| Variable | Description |
|---|---|
Qcache_hits | Number of cache hits (queries served from cache) |
Qcache_inserts | Number of queries added to the cache |
Qcache_not_cached | Number of queries that could not be cached |
Qcache_queries_in_cache | Number of queries currently in the cache |
You can also verify cache activity in the ApsaraDB RDS console:
Measure cache effectiveness
Use these formulas to decide whether the cache is helping or hurting:
Hit rate — the fraction of all SELECT queries served from cache:
Qcache_hits / (Com_select + Qcache_hits)A low hit rate may indicate that queries vary too much to benefit from caching, or that writes are invalidating cached entries faster than they accumulate.
Insert-to-hit ratio — how many times each cached result is reused:
Qcache_hits / Qcache_insertsA ratio below 1 means most cached queries are never reused — the cache is storing more than it serves.
Write overhead — the cost of invalidation relative to hits:
(Com_insert + Com_delete + Com_update + Com_replace) / Qcache_hitsA high ratio means writes are generating more cache invalidation work than the cache saves. Consider disabling the cache for this workload.