The MySQL Query Cache: How it works, plus workload impacts

The MySQL query cache is one of the prominent features in MySQL and a vital part of query optimization. It is important to know how the MySQL query cache works, as it has the potential to cause significant performance improvements – or a slowdown – of your workload.

The MySQL query cache is a global one shared among the sessions

It caches the select query along with the result set, which enables the identical selects to execute faster as the data fetches from the in memory. It is important to have everything identical, no new comments, spaces, or most significant differences in the WHERE clause. Basically when you trigger a select query, if it is available in the cache; it fetches from there or it considers the query as a new one and will go to the parser.

Even though it has some nice advantages, the MySQL query cache has its own downsides too. Well, let’s think about this: If you are frequently updating the table, you are then invalidating the query cache for ALL queries cached for that table. So really, anytime you have a “frequently updated table” means you’re probably not going to get any sort of good usage from the MySQL query cache. See the below example.

From the above we are sure the queries are cached. Let us try an insert and see the status, it will invalidate the query cache and reclaim the memory.

Now let us think about how to decide the MySQL query cache size:

To exemplify:  I am having a mysql instance with two tables “t” and “t1”. Table “t” is with numerous records and “t1” is with fewer records. Let’s restart the mysql and see the query cache details.

From the above status note the below four points.

1) There is around 1 MB free space with Qcache.

2) The queries in Qcache are zero.

3) There are no Qcache hits.

4) Qcache lowmem prunes is zero.

From the aforesaid status, it is clear the query has been cached and it should execute much faster in the next try and increase the Qcache hits status variable by one.

Now let us see how the data is getting pruned from the Qcache. For this, I will execute a select on table “t” which is having massive records.