How To Enable MySQL Query Cache

MySQL query cache stores query results of frequently and recently run queries so they can be returned quickly without processing everything from scratch. They are very useful in improving query speed and database performance. Here are the steps to enable MySQL query cache for your database, set MySQL cache size and disable query cache. Please note, MySQL query cache has been deprecated in MySQL 5.7 and removed in MySQL 8.0.

How To Enable MySQL Query Cache

Here is MySQL Query cache configuration for your database.

1. Check Availability

Log into MySQL and run the following command to check if MySQL query cache is available for your database.

mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

If you get the result as YES, it means your database supports query cache.

Bonus Read : How to Enable MySQL Slow Query Log

2. Default Query Cache Variables

MySQL query cache is controlled by many variables. Run the following command to view their default values first, before changing them.

mysql> show variables like 'query_cache_%' ;

You will see the following output

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_type             | OFF      |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+

Let us look at the above query cache variables,

query_cache_limit – maximum size of query results that can be cached
query_cache_min_res_result – MySQL stores query result in blocks. This is the minimum size of each block.
query_cache_size – indicates the total amount of memory allocated for MySQL cache.
query_cache_type – setting this to 0 or OFF disables MySQL query cache. setting it to 1 enables query cache.
query_cache_wlock_invalidate – determines if MySQL should fetch results from cache if the underlying table is locked.

Bonus Read : Top 5 MySQL Monitoring Tools

3. Enable MySQL Query Cache

Log out of MySQL. Open terminal and run the following command to open MySQL configuration file.

$ sudo vi /etc/mysql/my.cnf

Add the following lines below [mysqld] section

... 
[mysqld] 
query_cache_type=1 
query_cache_size = 10M 
query_cache_limit=256K

We have enabled query cache by setting query_cache_type variable to 1, with individual query cache as 256Kb and total cache as 10Mb. You can change the values of query_cache_size and query_cache_limit as per your requirements.

Bonus Read : How to Speed Up MySQL Queries