Learn about mysql query caching

Today’s topic is the query cache. What’s the query cache? The query cache is some mapping between a query syntax and output. MySQL is going to add both of them into a table in the cache. The first time, MySQL is going to execute the query and store the output the subsequent times instead of executing the query. MySQL is just going to grab the resource set from the mapping table or from the cache, making the query very fast to return its output because it doesn’t execute the query, it just grabs the resource set from the mapping table or from the cache. Here’s a situation where we have 800 queries waiting for the query cache log to be acquired. This is a common issue, a common pain point. It’s very important to understand what happens in the background. Let’s basically dive into the internals to better understand what happened. Let’s set profiling to one so that we have more information, more details about the queries we’re going to execute. I have this test table and then let’s do it just a simple, let’s say it takes half a second.

You can see the queries in the profile output or profile stable. Now let’s execute the query in exactly the same syntax. Notice that this time around, around it takes a millisecond. Let’s see what’s in the profile.

We have the two queries. Let’s dig into each of them. If we do show profile or query one, you notice that my SQL is going to check. The first time it doesn’t know if the query cache is in the mapping table or the cache. It’s going to check and then it’s going to wait for the query cache. Remember those processes were waiting. Every time you do a query, it’s not just a select, it could be an update. Even though they only select output into the query cache, but every time you do a select, it’s going to basically check if the output exists already. If it doesn’t, it’s going to execute the query. Otherwise, it’s just going to grab the output and return it to the client instead of executing the query.

Now, if we change the, let’s say we change the, we can just add a space.

It’s going to execute the query. That’s why the syntax is very sensitive and it’s very rudimentary. The query cache in my SQL because adding a space is treated as a new query. If you do show profiles, see it’s going to be a new query. If you do, it’s a third query.

It’s going to do the same thing, all same thing, checking and then waiting for query cache. Remember the second query took only a bit of second. Let’s check the internals. You can see that, for example, this is sending cached result to the client because it was cached so it did not execute it. Whereas the first time it stored it into the query cache. Now, let’s try to update the table. For the first query, we know the data is stored so it’s taking a millisecond. Let’s update the table. Now, let’s run the select again. MySQL is going to execute the query. What happened is that the update invalidated the output. Because every time you change the data into a table, MySQL is going to invalidate all of its outputs from the query cache. That means all of the queries in the query cache will have to be executed the next times because the output invalid. It’s not recommended to have a very big query cache.

Let’s show you here. We have 256 megabytes. That’s why you should keep it small because although if it’s big, we may end up storing more data into the query cache. But the flip side is when you update the table, it’s going to invalidate all of those outputs into the query cache. That’s basically the takeaway. The takeaway is to keep it as small as possible, typically 8 megabytes, 16 megabytes. Thank you for listening.