Introduction

Last time we talked about Hibernate’s second-level cache — that was thinking about caching from the “application / ORM” angle. This time we drill down one layer and look at how the database itself also has caching mechanisms.

A lot of people are puzzled the first time they hear “the database has a cache too.” Wasn’t the whole point of caching to avoid hitting the database? So why does the database have a cache inside it? There’s actually no contradiction. What the database has to deal with is disk I/O — something inherently slow — so of course it wants to keep frequently used data in memory and cut down on how often it actually touches the disk.

This article covers two things that happen to form a nice contrast:

1. The Buffer Pool: InnoDB’s core cache. It’s still the lifeline of performance today — you use it basically every day without realizing it.
2. The Query Cache: A “query result cache” MySQL once shipped with, but which was officially removed in MySQL 8.0.

Putting these two caching mechanisms side by side is a great way to understand “what kind of cache design lasts, and what kind gets left behind by the times.”

The Buffer Pool

What Is the Buffer Pool?

The Buffer Pool is a large region InnoDB carves out in memory to cache data pages and index pages.

To understand it, you first need to know that InnoDB’s basic unit of access isn’t “a single row” but a page, 16KB by default. When you query one row, InnoDB doesn’t just fetch that one row from disk — it reads the entire 16KB page that row lives in into memory.

And disk I/O is several orders of magnitude slower than accessing memory. So InnoDB’s strategy is intuitive:

Keep pages you’ve read or modified in memory (the Buffer Pool) as much as possible, so that accessing the same page later doesn’t have to touch the disk again.

This helps both reads and writes:

• Reads: If the page you need is already in the Buffer Pool (a cache hit), it’s returned straight from memory, never touching disk.
• Writes: A modification is first written to the page in memory (that page now becomes a “dirty page”), and is not immediately flushed to disk. Instead, a background thread flushes it in batches later. This means writes don’t have to wait on the disk every time.

This is the most fundamental difference from the Query Cache we’ll discuss shortly: the Buffer Pool caches a low-level structure — “data pages” — not “the result of some SQL statement.” Precisely because it works at such a low level, it can speed up almost everything.

How Does the Buffer Pool Decide What to Keep? An Improved LRU

Memory is finite, so once the Buffer Pool fills up, it has to decide “which page to evict” to make room. InnoDB uses an improved version of LRU (Least Recently Used).

Plain LRU has a classic problem: full table scan pollution. Imagine a reporting query that scans a huge table in one shot, shoving a pile of “only used this once” pages to the very front of the LRU list — pushing the genuinely hot data out and tanking the hit rate.

InnoDB’s fix is to split the LRU list into two segments:

• The New / Young sublist: holds the genuinely hot pages.
• The Old sublist: holds pages that were just read in and haven’t yet proven themselves to be hot. By default it takes up about 3/8 (37%) of the list, adjustable via innodb_old_blocks_pct.

The key is this: a newly read page is not inserted at the very front, but at the head of the old sublist (midpoint insertion). A page is only promoted to the young sublist if, “after entering the old sublist, it gets accessed again within a certain window.” That way, the one-off pages brought in by a full table scan just sit in the old sublist and get evicted quickly, never polluting the genuinely hot data.

A Few Settings and Observations You’ll Run Into in Practice

innodb_buffer_pool_size — the single most important parameter, deciding how big the Buffer Pool is. On a dedicated database server, a common recommendation is to set it to 50%–75% of physical memory. Too small and the hit rate drops and you keep reading from disk; too large and you may starve the OS and other processes.

Hit rate — you can check it via SHOW ENGINE INNODB STATUS, in the BUFFER POOL AND MEMORY section, where there’s a line like:

Buffer pool hit rate 1000 / 1000

meaning all of the last 1000 page accesses hit memory, with not a single disk read. On a healthy OLTP system this value is usually very close to a perfect score.

Change Buffer (a quick aside) — InnoDB has a related mechanism called the Change Buffer. For writes to “non-unique secondary indexes,” if the corresponding page isn’t currently in the Buffer Pool, it stashes the change first and merges it later, avoiding frequent random disk reads just to update an index. It works in tandem with the Buffer Pool; for now it’s enough to know it exists.

The Buffer Pool in one sentence: it’s the foundation of database performance. You don’t need to “decide whether to use it,” because you always are. What you can do is make it big enough and watch its hit rate.

The Query Cache

What Is the Query Cache?

Now that we’ve covered the Buffer Pool that’s alive and well, let’s look at the one that got left behind.

The Query Cache was a built-in feature of MySQL (before 8.0), and what it caches is at a completely different level from the Buffer Pool: it caches the “complete result set” of an entire SELECT statement.

Its logic looks very appealing at first glance:

1. On receiving a SELECT, it first takes the text of that SQL and checks the Query Cache for a hit.
2. If there is one (a cache hit), and none of the tables that statement touches have been modified since it was cached, it just throws back the previous result — no parsing, no optimizing, no executing at all.
3. If there’s no hit, it executes normally and stores the result in the Query Cache for next time.

The relevant settings looked roughly like this (again, pre-8.0):

# 0=OFF, 1=ON, 2=DEMAND (only cache queries with the SQL_CACHE hint)
query_cache_type = 1

# size of the cache area
query_cache_size = 64M

Sounds great, doesn’t it? Not even having to execute the query, just returning the result straight away — isn’t that the fastest possible? The catch is that the theory is beautiful, but in practice the trouble it brings often outweighs the benefit.

Why Was the Query Cache Removed?

The Query Cache was marked deprecated in MySQL 5.7.20 and officially removed in MySQL 8.0. The reason the maintainers made such a heavy call is that it had several structural problems that were hard to solve.

Problem 1: Invalidation Is Far Too Coarse-Grained

This is its most fatal flaw. The Query Cache’s invalidation rule is:

The moment any write (INSERT / UPDATE / DELETE) happens on a table, every cached query that touches that table is invalidated and wiped all at once.

Note: it’s not “the queries related to the row that was modified” that get invalidated — it’s every query related to the entire table, regardless of whether you touched the specific rows they queried.

On a “read-heavy, write-light” table this is fine, but the moment a table sees writes even a little more frequently, it becomes a disaster: the pile of query results you painstakingly cached can be invalidated in an instant by a single incoming write, then re-executed and re-cached next time, only to be wiped by the next write… The cache barely does anything, yet keeps doing the pointless busywork of “store it, wipe it.”

Problem 2: A Concurrency Bottleneck Caused by a Global Lock

To maintain this shared cache, the Query Cache needs a lock internally to protect it. The problem is that this lock is very coarse-grained — practically global in scope.

The result: in a high-concurrency, multi-core environment, a large number of threads contend for the same lock in order to “check the cache / write the cache / wipe the cache,” creating serious contention. Ironically, a feature meant to “speed things up” instead becomes the whole system’s serialization bottleneck under high concurrency, cancelling out the advantage of having multiple cores. The more cores and the higher the concurrency, the more pronounced this problem gets.

Problem 3: It Only Hits on an “Exact Character-for-Character Match”

The Query Cache’s matching is done against the raw string of the SQL, which means the hit conditions are extremely strict:

• Different case, one extra space, one extra newline, one extra comment → treated as a different query, no hit.
• Use of non-deterministic functions like NOW(), RAND(), CURRENT_TIMESTAMP → the result changes by nature, so it won’t be cached at all.
• Connections with a different database (schema), a different protocol version, or a different character set → may also be treated as different.

In other words, the slightly-differently-shaped SQL that real applications produce via an ORM or from different code fragments often can’t benefit from this cache at all.

Problem 4: Taken Together, It’s Often a Net Negative

Add up the points above and you’ll find that in many real-world workloads, the Query Cache does more harm than good:

• To maintain the cache you pay the cost of lock contention, memory management, and invalidation checks.
• But because invalidation is too coarse and the hit conditions too strict, the number of executions it actually saves is quite limited.

So it often ends up in an awkward spot: turning it on actually makes things slower. This is exactly why, back when it still existed, the first tuning recommendation from many senior DBAs was “turn the Query Cache off.” A feature that’s “best left off by default” being removed is hardly surprising.

So Who Took Over Its Role?

After the Query Cache disappeared, the “don’t re-execute identical queries” problem it was trying to solve was handed off to more suitable layers:

1. Application-layer caching (the mainstream): Use an external cache like Redis / Memcached to cache query results or computed results. Compared to the Query Cache, its invalidation strategy (TTL, active eviction) is under your precise control, it can be shared across machines, and its semantics are clearer — which is really the same trend as “the second-level cache being replaced by Redis” from last time: people prefer “explicit, controllable” caching over implicit caching that’s buried at a low level and coarse-grained to boot.

// The typical approach: application-layer caching with the Spring Cache abstraction + Redis
@Cacheable(value = "userProfile", key = "#id")
public UserProfile getUserProfile(Long id) {
    return userRepository.findProfile(id);
}

2. Leave it to the Buffer Pool + good indexes: A lot of the time you don’t need to “cache the whole result” at all. As long as the data pages and index pages are in the Buffer Pool and the indexes are well built, the query itself is already fast. Rather than caching the result, make the query itself fast and cheap — that’s more fundamental and more stable.

3. Proxy-layer caching: A database proxy like ProxySQL can also provide a far more controllable query cache outside of MySQL, to be introduced when needed.

Buffer Pool vs. Query Cache

Putting the two side by side makes it clearer why one stayed and one was left behind:

The difference in one line: the Buffer Pool makes “executing a query itself” faster, while the Query Cache tries to “not execute the query at all.” The former is solid and effective, helping every operation; the latter’s appeal only holds under ideal conditions and collapses the moment writes get frequent and concurrency gets high.

Wrap-Up

In this article we looked at the two caching mechanisms at the database layer side by side:

• ✅ The Buffer Pool is InnoDB’s core mechanism for caching data/index pages in memory, managed with an improved LRU (midpoint insertion to resist full-table-scan pollution), effective for both reads and writes — the foundation of performance. What you should do is make innodb_buffer_pool_size big enough and watch the hit rate.
• ✅ The Query Cache caches “the result of a whole SELECT.” Beautiful in theory, but because invalidation is too coarse, a global lock creates a concurrency bottleneck, and it only hits on an exact match, it often does more harm than good under real workloads, and was ultimately removed in MySQL 8.0.
• ✅ Its role was taken over by application-layer caching (Redis) + good indexes that make the query itself fast. This is the same trend as the second-level cache being replaced by Redis: moving from “implicit, coarse-grained, buried at a low level” toward “explicit, controllable, placed at the right layer.”

If what the second-level cache taught us last time was “the moment a cache’s underlying assumption stops holding, its risk outweighs its benefit,” then the Query Cache’s story adds one more lesson: whether a cache design lasts often hinges not on how fast it is when it hits, but on how painful it is when it invalidates, and how costly it is to maintain. The Buffer Pool endures precisely because its invalidation is precise and its cost controllable; the Query Cache was left behind for failing on exactly those two points.

Once you understand this layer, you’ll ask one more question when designing your own cache: when does it invalidate, and what’s the cost of that invalidation? And that is often more worth thinking through first than “how fast it is when it hits.”