Caching Expensive Queries in WordPress

I love WordPress. One thing to know about it, though, is that it queries MySQL for a lot of data. This is fine for most single sites, but at our institution we’re running a very large multisite install and every query or call I can reduce means I can generate pages faster. Faster is always better, and even though we’re doing WordPress caching with the WP Super Cache plugin, there’s always opportunities to improve the speed.

One way is help WordPress (which is built on PHP) and MySQL by caching some expensive queries, that is, queries that return large result sets or you join lots of tables together on certain values. In our case, we’ve installed APC on our dedicated server and are trying it out as a user and opcode cache for some WordPress data to help speed things up. Here’s some ways you can do the same:

On one of our page templates, we query WordPress for a custom post type, in our case a calendar of events. Since some of our sites have many events, this query can take a bit of time to execute. We can help out by storing the result of the MySQL in memory and calling that as needed.  Here’s some code:

$cachequery = "bb_".$blog->ID;
$cacheexpire = 1800;

if($mypost = apc_fetch($cachequery)){
 echo "<!-- cached query -->";
}else{
 $mypost = query_posts($args);
 apc_store($cachequery,$mypost,$cacheexpire);
 echo "<!-- caching this -->";
}

The first we do is declare a variable, $cachequery, which is specificially generated per site, using the blog’s ID and a prefix depending on what template and query we’re using. In our case, the bb_ stands for big button, a template that looks like this.

At this point we also define how long we want this data to be cached. In this case, I say 1800 seconds, which is 30 minutes. This could probably be longer, such as 3600 seconds, but I’m working to find a sweet spot of speed and making sure the user is always seeing good data.

On line 4, you’ll see that we need to do is see if APC and PHP are currently storing any data for our variable. If there is good data, it is stored in the $mypost variable, and we echo a response, which is optional.

If PHP doesn’t a result from APC, the data hasn’t been cached yet. So we run the query, store it in the $mypost variable, and write the value to the APC store. Also, if the data is older than 1800 seconds, APC will return an invalid response and the query will be run fresh. This happens because we set the expiration time in the apc_store function.

APC has a nice PHP page you can install that will give you a report of how APC is doing and what data is being cached. It makes some nice graphs, but also allows you to explore the user-generated data, such as what we cached above. Let’s look at one of our stored variables and what APC is actually caching.

If we view the detail for an entry, we can see what’s being stored, in this case the array that WordPress returns when we use the get_posts or query_posts functions.

I’ve been keeping an eye on WordPress and the APC status for a few days now, and all seems well. I think this is also something I’d look into when building a non-WordPress PHP application as well. Faster is always better.

2 thoughts on “Caching Expensive Queries in WordPress”

  1. This is definitely an interesting idea. With APC being a PHP extension, how does this behave with PHP’s memory limits (does the APC cache eat up any of the memory you have allocated for PHP)?

    For a lot of our custom plugins and functions at UMW, we’re using WordPress transients for a lot of our expensive processes (building the HTML output and storing it as a single transient element in the database).

Comments are closed.