Query Caching in MySQL

Something you might want to consider turning on query caching in MySQL. I’m not sure if this is what you are getting at, but MySQL has can be configured to cache query results in memory. That way, if you use the same query over and over again, it can feed the answer to it’s clients without accessing disk.

To see if query caching is already on:
mysql> SHOW VARIABLES LIKE ‘%query_cache%’;
+——————-+———+
| Variable_name | Value |
+——————-+———+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_size | 0 |
| query_cache_type | ON |
+——————-+———+
4 rows in set (0.00 sec)

This means query_cache_type is ON, but the query_cache_size is set to 0, so it’s really not running!

To turn it on (must have SUPER priviledge):
mysql> # You may want to use as much as 32 MB for this, if you have gobs
mysql> # of RAM (1 GB or more) and a ton of frequently run queries with big
mysql> # results, all just depends on how much RAM you want to give up
mysql> # vs how bad you need this option.
mysql> # I only have 512 MB of RAM in my server, and I use it for a lot of other things
mysql> # so I am only going to allow it to consume 2 MB
mysql> SET GLOBAL query_cache_size = 2 * 1024 * 1024; # 2 MB

Now when I run:
mysql> SHOW VARIABLES LIKE ‘%query_cache%’;
+——————-+———+
| Variable_name | Value |
+——————-+———+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_size | 2097152 |
| query_cache_type | ON |
+——————-+———+
4 rows in set (0.00 sec)

To make this permanent, you may want to add these lines to your my.cnf file so that it comes on after a restart:

set-variable = max_allowed_packet=1M
set-variable = have_query_cache=YES
set-variable = query_cache_limit=1048576
set-variable = query_cache_size=2097152
set-variable = query_cache_type=ON

You can read more about this topic on the MySQL website:
http://dev.mysql.com/doc/mysql/en/Query_Cache.html