The Basics of MySQL Query Caching

Introduction

Queries are ubiquitous in the life of every MySQL database administrator or even a database-savvy developer. As we have already stated in some of our previous blog posts, queries are simply tasks composed of smaller tasks. To optimize their performance, we should make those smaller tasks execute quicker or not execute at all. First, we must examine how MySQL performs its queries. We have already covered the basics of what makes queries slow in MySQL and we came down to the fact that we need to profile our queries-the query cache was one of the first things that MySQL looked at, remember?

What Is the Query Cache?

The MySQL query cache, though deprecated in MySQL 5.7 (and removed in 8.0), stores statements that have previously been run in memory: in other words, a query cache usually stores SELECT statements in the memory of a database. Therefore, if we run a query and then run precisely the same query again after a while, the results will be returned faster because they will be retrieved from memory and not from the disk.

The query cache caches both the query and the result set. Thus, when we run the same query, the results from the query cache are returned instantaneously. The query cache size can be controlled by setting the query_cache_size system variable, but here's the caveat: if you want your queries to make use of the query cache, they must, must, must be identical, byte by byte. That means that even though you would think that these two queries should be cached in exactly the same way:

In reality, they're not. They are not because the MySQL query cache requires all of its queries to be the same and will not return any results if they differ even by one byte.

So, to sum this up, when MySQL executes statements, one of the first things it does is check whether the query cache is enabled (come back to our last blog post on slow MySQL queries if you need a refresher.) If the query cache is enabled, MySQL will first check for any relevant matches to the query there; if there are no matches, MySQL will go on to the next step. However, if there are identical matches, MySQL will return results from its query cache.

Inside the MySQL Query Cache

Prior to returning matches, the MySQL query cache asks MySQL for confirmation: Does this user have the necessary privileges to complete such an action? Should I deny the execution of the query?

Here's a list of privileges MySQL checks:

There are more privileges, but you get the idea. Privileges are important for the query cache because MySQL also stores information relevant to the table together with the cached query. Privileges are also crucial because it's one of the first stages of the result checking process for MySQL. Here's how everything works:

"It's Not Cached!": Is the Query Cache Broken?

When functions or partitions are used, and if we use any variables inside our queries, the query cache will not be effective either. If we are working with big data, for example, and we use SELECT ... INTO OUTFILE to load big data sets into MySQL, the result will similarly not be cached. Generally, the query cache does not work when queries like SELECT ... [LOCK | FOR | INTO] are in use, the query cache also cannot be used if we are checking for NULL values in columns with AUTO_INCREMENT values meaning that if we have a column increment with an AUTO_INCREMENT on it, and we run a query like so:

SELECT * FROM demo_table WHERE increment IS NULL;

The query cache will not go into effect either.

All this functionality might have some of you shouting "It doesn't work!"-and you're right. The query cache does not always go into effect: as with everything in MySQL, it has limitations too. Aside from those above, it refuses to work when statements generate warnings or statements are run on tables with the TEMPORARY storage engine and also in some other corner cases. The query cache empties every time anyone writes anything to the table too.

The functionality of the query cache also depends on the query_cache_size parameter. The bigger this parameter is, the better, but note that this parameter heavily depends on your memory. Keep in mind that the query cache needs at least 40kB of storage for its basic structure by default, and its default values may range between 1MB and 16MB. However, if your database is very busy reading data, a bigger value may help-to find an optimal solution, you again have to experiment.

You can also remove everything in the query cache by running the RESET QUERY CACHE statement. If you use a statement like FLUSH TABLES, all values will be removed from the query cache too.

MySQL even allows you to see how many queries are stored in the query cache by running the query:

SHOW STATUS LIKE 'Qcache_queries_in_cache%';

You should get results that look something like this:

+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_queries_in_cache | 1      |
+-------------------------+--------+

In this case, we can see that our query cache currently caches one result. Execute a statement like FLUSH TABLES and all values will be gone. Magic.

Summary

In MySQL, the query cache is a very powerful beast. It's usually able to store a wide variety of values and return them from the memory instead of returning them from the disk in order to make query execution time fast. However, it's not without its flaws; the query cache has some limitations, but you should be fine if you know what you are doing.

The query cache is mostly useful with SELECT queries - for that, do have a look at some of our other blog posts. We have already discussed how you should go about working with big data inside of MySQL, how to optimize your schemas, and even how to use advanced MySQL operations.

Lukas is an ethical hacker, a MySQL database administrator, and a frequent conference speaker. Since 2014, Lukas has found and responsibly disclosed security flaws in some of the most visited websites in Lithuania and abroad including advertising, gift-buying, gaming, hosting websites as well as some websites of government institutions. Lukas runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com and frequently blogs in multiple places educating people about information security and other topics. He also runs his own blog over here.