Enable and Test MySQL Query Cache

Written by Peter Davies on .

Edit the config file and add the query cache lines:

nano /etc/mysql/my.cnf
query-cache-type = 1
query-cache-size = 200M
/etc/init.d/mysql restart

To test it's functioning you will need to add an extra phrase to the SQL statement called "SQL_CACHE". This will create SQL statements looking like:

SELECT SQL_CACHE * FROM my_table;

You can then test that its doing something by simply running the following queries after repeating the above query a few times:

SHOW VARIABLES LIKE ‘%query_cache%’;
SHOW STATUS LIKE ‘%qcache%’;

You can see that the status output shows that after executing the statement a few times that the cache is being hit: