How to enable MySQL query cache

There are multiple ways to optimize web page, loading speed. If an application depends on MySQL, one of the ways is to configure and use MySQL built-in query caching mechanism.  It's a memory based(RAM) caching system.

It is very easy to configure MySQL caching and can be really helpful in running queries, especially when the queries are demanding. 

How to enable MySQL query cache ?

Firs we can check if query cache is enabled:

mysql> SHOW VARIABLES LIKE 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | OFF   |
+------------------+-------+

You can see that it is OFF, so first thing to do is to enable it.

query_cache_type setting can have 3 values:

  1. A value of 0 (or OFF) means that the query cache is disabled.
  2. A value of 1 (or ON) means that queries will use cache as long as SQL statement doesn't begin with SELECT SQL_NO_CACHE.
  3. A values of 2 (or DEMAND) means that caching will be used only for SQL statements beginning with SELECT SQL_CACHE.

We can enable cache by setting query_cache_type to 1 by either running: 

set global query_cache_type=1; in mysql console or we can modify configuration file *.cnf and add the same string: query_cache_type = 1 to keep this setting with MySQL server restarts.

Setting MySQL cache size

Another parameter query_cache_size determines the max size of cache. It can also be set either by running in sommand line : set global query_cache_size = 33554432;  (32MB) or modifying MySQL config: 
query_cache_size = 32M

How to check if query cache is used ?

First, we can restart MySQL server and run one more SQL command to check if everything is properly set up:

mysql> SHOW VARIABLES LIKE 'query_cache_%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 33554432 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0.00 sec)

You can see that query_cache_type is ON and query_cache_limit is 33554432 (32MB), so everything should work.

There are several ways to check if our queries use cache. Before you run the following command to test if caching works, be sure to run some queries so that MySQL can cache them.

mysql> show status like 'Qcache_queries_in_cache';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 40    |
+-------------------------+-------+
1 row in set (0.00 sec)

You can see that in this scenario Qcache_queries_in_cache = 40, this means that 40 queries have been registered in query cach. 

Warning! MySQL query cache feature will be removed in MySQL 8.