The MySQL Query Cache
By Pete Freitag
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
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
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
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
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'
The MySQL Query Cache was first published on June 21, 2005.
If you like reading about mysql, cache, caching, performance, or databases then you might also like:
- 9 Ways to Save Bandwidth on your RSS Feed
- MySQL Optimization Hints
- MySQL DELAY_KEY_WRITE Option
- Insert Delayed with MySQL