The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.

The query cache can be useful in an environment where you have tables that do not change very often and for which the server receives many identical queries. This is a typical situation for many Web servers that generate many dynamic pages based on database content.

Lets begin! but before we start you need to make sure if your current MySQL version really supports it, in order to check you will need to type following command and will need to hit enter : Make sure you login first using root login info, once logged :

 sudo mysql -u root -p 

For checking caching support:

show variables like 'have_query_cache';

In case its supported you should expect response like this.

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
1 row in set (0.01 sec)

Configure Query Cache:

Please make sure first you are in the root directory if not cd to root / first then type following command and hit enter:

sudo nano /etc/mysql/my.cnf

Add following info at the end of the file.

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

Now that query_cache_type is set to 1 & individual query limit size to 256K and MySQL to allocate 10 MBs to query cache by setting the value of query_cache_size to 10M.

In order to save the file use CTRL + O then hit enter and to CTRL + X in order to close and restart the server.

sudo systemctl restart mysql
(Visited 39 times, 1 visits today)