This site is hosted and sponsored by hyve.com specialists in Cloud Hosting UK and VMware Hosting. If you are interested in our services please call us for chat on 0800 612 2524

The following article describes how to enable the MySQL Query Cache

There are two main configurations directives as follows:

query_cache_size=<number>

This is the memory allocated for caching query results.
Default = 0, which disables the query cache.


query_cache_type=<number>

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


To do the above here is the syntax:

Setup caching as follows:

$ mysql -u root –p

Output:

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

Set cache size 24Mb:

mysql> SET GLOBAL query_cache_size = 25165824;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'query_cache_size'; 

+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 25165824 |
+------------------+----------+

You can setup them in Redhat: /etc/my.cnf

# vi /etc/my.cnf

Configuration directives are as follows:

query_cache_size = 25165824
query_cache_type=1
query_cache_limit=1048576

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