Enable Query Cache in MySQL

If you want to get optimized and speedy response from your MySQL server then you can enable caching by using following two configurations directives to your MySQL server:


The amount of memory (SIZE) allocated for caching query results. The default value is 0, which disables the query cache.


Set the query cache type. Possible options are as follows:
0 : Don’t cache results in or retrieve results from the query cache.
1 : Cache all query results except for those that begin with SELECT S_NO_CACHE.
2 : Cache results only for queries that begin with SELECT SQL_CACHE

There are two ways to enable caching in MySQL

1. By logging in to MySQL interface:

$ mysql -u root –p
Enter password:

Now setup cache size 16Mb:
mysql> SET GLOBAL query_cache_size = 16M;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE ‘query_cache_size’;
| Variable_name    | Value    |
| query_cache_size | 16777216 |

2. You can setup them in /etc/my.cnf (Red Hat) or /etc/mysql/my.cnf (Debian) file:

vi /etc/my.cnf
Append config directives as follows:
query_cache_size = 16M
query_cache_type = 1
query_cache_limit = 1M

In above example the maximum size of individual query results that can be cached set to 1 MB using query_cache_limit system variable. Memory size in MB.

To check Query Caching Working run the following command:


Leave a Reply

Your email address will not be published. Required fields are marked *