What is MySQL Query Caching?

As we know, caching is used to improve performance. It will increase loading speed of the website or application. There are various types of caching mechanisms available. The Query Caching is one of the caching mechanism used to improve performance.

For MySQL, there is a general query cache which can help tremendously. The MySQL is one of the important feature in MySQL and an inevitable part of query optimization. After the caching, results will be set in a memory cache like memcached or cassandra. The query caching is normally used with the content caching. As with content caching, it is most effective in read-heavy scenarios. The MySQL query cache in particular should not be relied upon for performance as it is easy to invalidate large segments of the cache with minor changes to data. The MySQL query cache is a global one shared among the sessions.

Configuration Directives

The responses from the MySQL server can be slows sometimes. By using the query caching, the response from this MySQL servers can be optimized. This is the benefit of query caching. In this article, we are going to see how to enable the query caching in an MySQL server. In order to enable the query caching, we need to add the following configuration directives.

1) query_cache_size=SIZE

2) query_cache_type=OPTION

query_cache_size=SIZE

The first directive that is needed to be enabled to enable query caching in MySQL servers is the “query_cache_size=SIZE”. This directive enables us to set the size of memory or the amount of memory allocated for caching query results. In a typical server, the default value for this directive will be ‘0’. It denotes the query cache is disabled. The query caching will be disabled by default. To enable the query caching we need to set some value to this. We should set the value according to how much memory we are planning to allocate for query caching.

query_cache_type=OPTION

The next configuration directive that is needed to be set to enable query caching is “query_cache_type=OPTION”. Using this directive, we specify which type of query cache we are setting. There are 3 possible options that can be set for this directive. They are listed and explained below.

1) The zero “0”

2) The one “1”

3) The two “2”

These are the values that can be set for this directive. Let’s have a look on what these values represent.

The zero “0”

The zero tells the server the following: Don’t cache the results in or retrieve results from the query cache.

The one “1”

There are query results that starts with “SELECT S_NO_CACHE”. If we set the value for this directive as one, it represents that, to cache all the query results except for those that begin with SELECT S_NO_CACHE.

The two “2”

The other value we can set for this directive is “2”. If we set the value as “2”, it means that cache results only for queries that begin with SELECT SQL_CACHE.

Enabling Query Caching in MySQL

We can setup the caching in the following format. You need to enter into MySQL with the following command. This means that you are entering into MySQL as the root user after you enter the command, the system will ask for the password.

# mysql -u root -p

When you run the above command, you will get an output like the one below.

Enter password:

Now you need to enter the root password. If the entered password is correct system will log you in and you will get an output like this:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 16 to server version: 4.1.15-Debian_1-log

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>

Assume that, we are setting up the query cache for 32 Mb. We need to do the following to acquire this.

mysql> SET GLOBAL query_cache_size = 33554432;

Query OK, 0 rows affected (0.00 sec)

To verify, we can run the following MySQL command:

mysql> SHOW VARIABLES LIKE ‘query_cache_size’;

+——————+———-+

| Variable_name    | Value    |

+——————+———-+

| query_cache_size | 33554432 |

+——————+———-+

Now, we can append other configuration directives as follows:

query_cache_size = 268435456

query_cache_type=1

query_cache_limit=1048576

The above commands mean that the maximum size of individual query results that can be cached set to 1048576 using query_cache_limit system variable. The memory size is set in Kb.

This is how we can set the query caching in the MySQL.