MySQL Query Cache

MySQL has a query cache which caches the results of SELECT queries, if enabled. This means that frequently used database queries will run much faster, because the data resultset will be read from the cache instead of having to run the query again. The MySQL query cache is available from MySQL 4.0.1. Whenever tables in the database are modified the relevant entries in the query cache are flushed so you can be certain that even with the query cache enabled only up to date data is returned.
You can tell if the query cache is enabled, and what parameters are set, by running the following query in MySQL:

SHOW VARIABLES LIKE '%query_cache%'

An example result for the above query is below, which shows that the query cache engine is available, but the query cache size is set to zero and therefore nothing will be cached, and the query cache engine will not actually be used.

+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+

It is possible to set the query_cache_size variable without actually restarting the MySQL server, by running the following SQL query. In this example, we are enabling a 50MB query cache.

SET GLOBAL query_cache_size = 50*1024*1024;

Running SHOW VARIABLES LIKE '%query_cache%' will now return the following:

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 52428800 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+

This means a query cache will now be running, using 50MB of memory. However, the next time the MySQL server is restarted the setting will be lost and query cache will no longer be used. Next, we will look at how to make the change permanent.

Setting the MYSQL query cache size in my.cnf

In order to make the query_cache_size setting permanent, the MySQL server configuration file must be modified. These settings are stored in a file called my.cnf which is typically stored on a Linux system at /etc/my.cnf or sometimes at /etc/mysql/my.cnf. If it's not at either of those locations you can try running locate my.cnf or find / -name my.cnf, although note the latter command will take a while.
To enable the query cache with a 50MB query cache in MySQL, you would add the following line to the my.cnf file, under the [mysqld] section:

query-cache-size = 50M

The next time MySQL is restarted it will have the query cache enabled with the size specified.

MySQL Query Cache Speedup Example

Yesterday I posted an SQL query which works out the top selling categories in an ecommerce website. When I first ran this query on my local machine it was reasonably fast, but it took almost 4 seconds to run on the actual production server which is unacceptably slow. It had taken even longer but I optimized the query by adding appropriate indexes etc and only managed to get it down to 4 seconds.
After enabling the query cache, the first time the query was run it would still take almost 4 seconds, but subsequent queries for the same data took around 0.0001 seconds. This is much better and shows the power of the MySQL query cache.
The MySQL manual contains more information.