A query cache is a mechanism that stores the result set of a query and returns that stored result when the same query arrives again, so the database or the application does not have to parse, plan and execute it a second time. In database performance work, it is one of the oldest and most tempting optimizations, because a cache hit can turn a query of several hundred milliseconds into a lookup that takes well under a millisecond.
The idea is simple, but the hard part is keeping cached results correct. Every write to the underlying tables can make a stored result stale, so each query cache design is defined less by how it stores results than by how it invalidates them. That trade-off explains why some databases removed their built-in query cache while application-level result caching remains common in production systems.
How a query cache works
A query cache works by turning each query into a cache key, looking that key up in memory, and either returning the stored rows or executing the query and saving its result. The key is usually built from the exact query text together with everything that can change the answer: the current database or schema, the session character set, the user’s privileges and the values of any bound parameters.
On a cache hit, the server skips parsing, optimization and execution entirely and streams the stored rows to the client. On a cache miss, the query runs normally, and the result is inserted into the cache if it qualifies. Results that depend on non-deterministic functions such as the current time, random numbers or user variables are not cacheable, because the same text can legitimately return different rows.
The following table shows the stages a request passes through and what a hit saves at each stage.
| Stage | Work on a miss | Work on a hit |
|---|---|---|
| Key construction | Normalize or hash query text and context | Same, always required |
| Parsing | Tokenize and build a syntax tree | Skipped |
| Optimization | Choose an execution plan | Skipped |
| Execution | Read pages, join, sort, aggregate | Skipped |
| Result delivery | Send rows to the client | Send stored rows |
Invalidation and consistency
Invalidation is the process of removing cached results that may no longer be correct, and it is the defining design decision of every query cache. The simplest rule, used by the classic MySQL query cache, is table-level invalidation: any insert, update or delete on a table discards every cached result that read from that table, even results the change did not affect.
Table-level invalidation is always correct but becomes expensive on busy tables. A table that receives a write every few milliseconds has its cached results thrown away almost as soon as they are stored, so the cache adds bookkeeping overhead without producing hits. Application caches often choose time-based expiry instead, accepting results that may be a few seconds old in exchange for far fewer invalidations.
| Strategy | How it decides staleness | Main risk |
|---|---|---|
| Table-level invalidation | Any write to a referenced table | Low hit rate on write-heavy tables |
| Time to live (TTL) | Fixed age limit, often 1 s to 1 h | Serving stale data until expiry |
| Explicit eviction | Application deletes keys after writes | Missed eviction leaves wrong data |
| Versioned keys | Key includes a version counter | Orphaned entries waste memory |
| Change-data-driven | Change stream triggers eviction | Lag between commit and eviction |
Consistency also interacts with transactions. A result cached by one session must not be visible to another session that should not yet see uncommitted changes, so built-in caches typically bypass caching inside open transactions that have already modified a table.
Built-in caches versus application result caching
A query cache can live inside the database server or in front of it, and the location changes who is responsible for correctness. A server-side cache sees every write and can invalidate automatically, while an application-side cache in an in-memory key-value store scales independently but relies on the developer to evict entries correctly.
The MySQL query cache illustrates the limits of the server-side approach. It used a single global mutex, so on machines with many cores, concurrent sessions waited on the lock even when the hit rate was good. It was deprecated in MySQL 5.7 and removed in MySQL 8.0. PostgreSQL never shipped a result cache of this kind and relies on its shared buffers and the operating system page cache instead.
| Layer | What it stores | Saves |
|---|---|---|
| Query cache | Complete result sets keyed by query | Parsing, planning and execution |
| Plan cache | Compiled execution plans | Parsing and optimization only |
| Buffer pool | Data and index pages | Disk reads, not computation |
| Materialized view | Precomputed query output in a table | Execution, refreshed on schedule |
| Application cache | Serialized objects or result sets | Database round trips entirely |
These layers are complementary. A buffer pool helps every query by keeping hot pages in memory, while a query cache helps only exact repeats. A materialized view is closer in spirit, but it is a stored relation that the planner can use for different queries and that is refreshed explicitly rather than invalidated per write.
When a query cache pays off
A query cache pays off when the same expensive queries repeat often and the underlying data changes rarely relative to how often it is read. Dashboards, product catalogs, reference tables and reporting pages that many users load with identical filters are classic candidates. A useful rule of thumb is to compare the read-to-write ratio of the tables involved with the cost of the query being saved.
| Situation | Recommendation | Reason |
|---|---|---|
| Read-mostly reference data | Cache with long TTL or table invalidation | Few writes, many identical reads |
| Heavy aggregate on large tables | Cache or use a materialized view | Execution cost dominates |
| Frequently updated order tables | Do not cache results | Invalidation outpaces hits |
| Per-user queries with unique filters | Usually skip caching | Keys rarely repeat |
| Slow query fixable by an index | Fix the query first | Caching hides the root cause |
Measuring is essential before and after enabling a cache. The hit ratio, the number of evictions due to low memory and the latency of misses all matter. A hit ratio below roughly half on a busy cache usually means it is consuming memory and lock time without a matching benefit, although the right threshold depends on how expensive the cached queries are.
Pitfalls and common mistakes
The most frequent mistake is treating a query cache as a substitute for query optimization. Caching a slow query hides the problem only for repeated requests, and every miss still pays the full cost, which often shows up as latency spikes after invalidation.
- Text sensitivity: in exact-text caches, differences in whitespace, letter case or comments produce separate keys for logically identical queries.
- Memory fragmentation: variable-size results stored in a fixed pool can fragment memory, reducing the usable cache size over time.
- Cache stampede: when a popular entry expires, many sessions miss at once and run the same expensive query simultaneously.
- Stale reads: TTL-based and application caches can return data that no longer matches the database, which matters for balances, stock levels and permissions.
- Lock contention: a global cache lock can make throughput worse on many-core servers even when hits are frequent.
Mitigations include normalizing query text or using prepared statements, applying request coalescing so only one session recomputes an expired entry, adding small random jitter to expiry times, and excluding data where correctness outweighs speed.
Put simply
A query cache is like a librarian who remembers answers to questions people ask often. When someone asks the same question again, the librarian answers from memory instead of searching the shelves, which is much faster.
The catch is that the books can change. If a new edition arrives, the remembered answer may be wrong, so the librarian must forget it and look again. A query cache is fast when answers rarely change and becomes a burden when the shelves are rearranged every few minutes.
In practice
An online retailer runs a category page that shows product counts and price ranges for about 40 categories. The aggregate query scans a products table of several million rows and takes around 400 ms, and the page is requested thousands of times per hour, while product prices are updated in a nightly batch.
The team stores the result of each category query in an in-memory cache with a TTL of 10 minutes and evicts the keys explicitly when the nightly import finishes. To avoid a stampede, only one worker recomputes an expired key while the others briefly serve the previous value.
After the change, most category requests are served in about a millisecond, database CPU during peak hours drops noticeably, and the query runs only a few times per hour. The order history page, which changes with every purchase, is deliberately left uncached.