The MySQL Query Cache

I have been reading about MySQL's Query Caching features in the High Performance MySQL book (ISBN 0596003064), and on the web. I have also been playing around with it on my own. I have concluded that it is a pretty cool feature! You will need MySQL 4.0.1 or higher to play...

I think what I like best about it is that the cache expires automatically when the table is modified (inserts, updates, delete's, etc). So it may not be terribly harmful to just enable the cache, and see what happens.

The High Performance MySQL book states that the Query Cache identifies cacheable (is that a word?) queries by looking for SEL in the first three characters of the SQL statement. However in my testing I found that whitespace or comments before the SELECT statement did not have any effect on caching. Perhaps the JDBC driver trims whitespace and comments before sending the SQL to the server.

Enabling MySQL Query Cache

Edit your my.cnf and set query_cache_type equal to 1, and set the query_cache_size to some value (here we have set it to 25mb)

query_cache_type = 1 
query_cache_size = 26214400

If either query_cache_type or query_cache_size are set to zero caching will not be enabled. If you have lots of RAM on your server you may want to increase the size of the cache accordingly. There are some more settings you can tweak but these will get you going.

Note you can also edit these settings using MySQL Administrator. They can be found under Health > System Variables > Memory > Cache

Cache Hints

You can also set query_cache_type = 2 - with this setting queries are only cached if you pass the hint SQL_CACHE to them, for example:

SELECT SQL_CACHE something FROM table

Alternativly, if you have query_cache_type = 1, you can tell MySQL that you don't want a specific query to be cached. This is highly recommended because you don't want to fill up the cache with highly dynamic queries (such as a search form). This is done with the hint SQL_NO_CACHE.

SELECT SQL_NO_CACHE stuff FROM table

Making the hints database independent

If your like me, you cringe at the thought adding database server specific SQL code to your queries. The High Performance MySQL Book has a tip that will allow you to use the hints and not break compatibility:

SELECT /*! SQL_NO_CACHE */ stuff FROM table

This trick will also work with the SQL_CACHE hint. And if you are really like me you will miss that ! in there, don't forget that or it won't work.

MySQL Query Cache and Prepared Statements

Some very good news is that MySQL Query Cache does seam to work well with prepared statements. In ColdFusion if you use the CFQUERYPARAM tag your using prepared statements. ColdFusion's builtin query caching mechanism does not allow queries with CFQUERYPARAM to be cached. They can be cached with MySQL Query Cache however.

So if you have some code such as this:

SELECT stuff FROM table 
WHERE name = <cfqueryparam value="#url.name#">

The SQL statement looks like this:

SELECT stuff FROM table
WHERE name = ?

Buy MySQL is smart enough to cache this query:

SELECT stuff FROM table
WHERE name = 'bob'