A lot of the stuff I host relies on running lots of queries over and over. Often, the queries are the same. And for the longest time, I had no idea of a very valuable MySQL feature: query caching!

Query caching will cache the results of queries, so that, if you run the same query again, it just serves it out of the cache, without the overhead of running the query. This can bring a huge speedup. It’s also smart enough to manage the cache: if you add/update/alter any of the data a query accesses, that query is removed from the cache.

There are a few limitations to be aware of, though:

• It’s not on by default!
• There is some overhead in running the query cache. MySQL.com estimates 13% worst-case. In practice, I’ve found that the speedup usually far outweighs the overhead of using the cache, but it depends on your implementation. Some people who don’t do much in the way of repeated reads have complained that the query cache was disastrous for performance.
• Not all queries benefit from caching:
  • Things involving the current time, which account for most of WordPress queries.
  • [Disputed] Queries involving variable-length fields, like TEXT and BLOB types?
  • If you’re dealing with tremendous amounts of cached data, older cached queries will be purged from the cache to make way for new ones.

Note that the middle one is disputed: I’m positive I’ve read it before, and that it made sense. But I can’t find it mentioned anywhere now, and some people more knowledgeable than I am on the matter have suggested that it’s not the case. I include it here because my gut tells me I’m at least partially right. But then again, my gut’s wrong a lot, too.

Enabling the query cache is pretty straightforward, though. Pull up your my.cnf file (/etc/mysql/my.cnf on Gentoo) and find the [mysqld] section. (The latter step is important: don’t put it in the [client] section, the [mysql] section, or the end of the file. It needs to be in the [mysqld] section!)

You want to add three lines:

query_cache_type = 1

query_cache_size = 16M

query_cache_limit = 5M

MySQL documents the query cache here. Here’s a blow-by-blow of what the lines do:

• The first line, sets the cache to type “1,” which is a ‘normal’ on. All queries, except those that you explicitly tell it not to cache (“SELECT SQL_NO_CACHE FROM…”) get cached. There’s also a type 2, which enables the cache but only for queries that explicitly use it (“SELECT SQL_CACHE…”). 2 might be ‘safer,’ but I’m yet to find a single downside in keeping it enabled.
• The second line tells MySQL that the total cache cannot exceed 16MB. You can fine-tune this number, but note that setting it arbitrarily large isn’t really a good thing: it takes memory away from other things. It should really be just big enough to house what you need. You should not assume that my setting of 16MB is ideal for you!
• The third line sets a limit of 5MB on any one cache result. This is meant to ensure that no one query can hog the cache. Even moreso than above, you should not assume that 5MB is the right value for you. (You could also argue that queries producing over 5MB of data are the ones that you really, really want to cache, so if you’re really trying to tweak performance, give this setting some thought.)

Once you’ve saved the file, restart mysqld (“/etc/init.d/mysql restart” on Linux) and go from there. There’s one variable and several status variables that tell you a lot about the status of the cache:

• have_query_cache will tell you whether the query cache is enabled. (“SHOW VARIABLES LIKE ‘have_query_cache'”)
• Status variables starting with Qcache are about the Q(uery) cache. (“SHOW STATUS LIKE ‘Qcache%'”);

phpMyAdmin’s server status page has a great presentation of this.