What is MySQL Query Caching?

Regarding MySQL, there is a general query cache which can greatly benefit. The MySQL holds significant importance in MySQL and is an unavoidable part of query optimization. Post caching, results will be stored in a memory cache like memcached or cassandra. The query caching is ordinarily utilized in conjunction with the content caching. Just like content caching, it is most efficient in scenarios that involve a high volume of reads. The MySQL query cache, in particular, should not be heavily 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 cache that is shared among the sessions.

 Configuration Directives

The responses received from the MySQL server can sometimes be slow. By utilizing query caching, the responses from these MySQL servers can be optimized. This is the advantage of query caching. In this article, we will explore how to enable query caching in a MySQL server. To enable query caching, the following configuration directives need to be added.

1) query_cache_size=SIZE

2) query_cache_type=OPTION

query_cache_size=SIZE

The first directive that needs to be enabled for query caching in MySQL servers is the “query_cache_size=SIZE”. This directive allows us to specify the size of memory or the amount of memory allocated for caching query results. In a typical server, the default value for this directive is ‘0’. This signifies that the query cache is disabled. The query caching functionality will be disabled by default. To enable query caching, we need to assign a value to this directive. The value should be determined based on how much memory we plan to allocate for query caching.

query_cache_type=OPTION

The next configuration directive that needs to be set to enable query caching is “query_cache_type=OPTION”. Through this directive, we specify the type of query cache that we are configuring. There are 3 possible options that can be assigned to this directive. They are listed and described below.

1) The value zero “0”

2) The value one “1”

3) The value two “2”

These are the assignable values for this directive. Let’s examine what these values represent.

The value zero “0”

The value zero conveys the following: Do not cache the results in or retrieve results from the query cache.

The value one “1”

There are query results that begin with “SELECT S_NO_CACHE”. If we set the value of this directive to one, it implies that all query results will be cached, except for those that start with SELECT S_NO_CACHE.

The value two “2”

Another value that can be assigned to this directive is “2”. If we set the value as “2”, it indicates that results will only be cached for queries that begin with SELECT SQL_CACHE.

Enabling Query Caching in MySQL

We can configure caching using the following format. You need to enter MySQL with the following command. This means that you are accessing MySQL as the root user, and after entering the command, the system will prompt for the password.

# mysql -u root -p

When you execute the above command, you will receive an output similar to the following.

Enter password:

Now you need to enter the root password. If the entered password is correct, the system will log you in and you will receive 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>

Assuming that we are configuring a query cache of 32 Mb, we need to perform the following steps.

mysql> SET GLOBAL query_cache_size = 33554432;

Query OK, 0 rows affected (0.00 sec)

To confirm, we can execute 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 signify that the maximum size of individual query results that can be cached is set to 1048576 using the query_cache_limit system variable. The memory size is specified in Kb.

This is how query caching can be enabled in MySQL.

If you require further assistance, please contact our support department.