MyLinuxTips

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:

query_cache_size=SIZE

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

query_cache_type=OPTION

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
Output:
Enter password:

mysql>
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’;
Output:
+——————+———-+
| 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:

SHOW STATUS LIKE ‘Qcache%’;