Why MySQL 8 drops support of query cache

Many of you may have heard or used MySQL's query cache, because it used to be a popular way to improve MySQL's performance. As an important feature for improving MySQL's performance, the query cache was often recommended as a solution for slow queries. However, why has MySQL 8 abandoned the query cache? Today, we will analyze and explore this decision.

What is query cache?

According to official document:

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client. The query cache can be useful in an environment where you have tables that do not change very often and for which the server receives many identical queries. This is a typical situation for many Web servers that generate many dynamic pages based on database content. The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.

What problems can occur when there is high concurrency in MySQL?

Now, let's assume a situation where there is high concurrency in MySQL. What problems can arise with the query cache? For example, if one client process is reading and another client process is writing to the same data table, should the query cache be read or not? Controlling all of this requires MySQL to spend a lot of effort, such as introducing locks. When there is a change, it is locked, preventing reads, and then when the change is complete, the cache is regenerated and given to the read side. With high concurrency, the query cache alone can become the biggest performance issue in MySQL. It not only fails to improve performance, but can also make our application slower.

There is very little we can do about query cache

For those who have used MySQL query cache, they should know that we can only use a few configuration parameters provided by MySQL. We cannot control the query cache control, cache hit rate, or anything else. All of these controls are managed by MySQL itself, so we are just users.

This is not conducive to our programming and business development needs. For us, the query cache is like a tool. When we encounter problems, we can only submit them to MySQL. Of course, we can also modify the source code of MySQL, but this requires a very high level of ability.

There are many benefits to storing cache on the client side

Nowadays, we see that Redis has become very mature, and it is introduced more or less in projects. For example, Redis can be used to implement functions such as saving APP login status, SMS verification code, and flash sale. 

Here, we introduce a question: Why can we use Redis to implement so many functions? One of the most important reasons is that we control everything. We can decide when it expires, cache what data, delete or replace a cache entry, and of course, there are many other control permissions. All of this is exactly what MySQL's query cache cannot provide. Compared with the cache on the client side, MySQL's query cache is really not very useful.