When using WordPress, I often find myself needed to query out posts or other information that isn’t easily available courtesy of one of WordPress’ built-in functions. While it’s a normal thing to do, it can come with a cost: performance.

Every time a query is written to pull out specific data from WordPress, it adds another query to MySQL which adds time and performance. Singularly, it’s not a huge cost, but if your site has any decent level of traffic, it can add up.

Screen Shot 2013-12-09 at 10.56.01 AMCase in point: we recently relaunched our internal campus news site as a responsive site.

Every day, we post news stories for various audiences and some of these stories are for events happening today, which is information we wanted to feature for our readers.

We have a custom field in our WordPress backend that allows us to note the day of an event, so even if the story is posted a month ago, on the day of the event we can query it and display it for the user.

That query is pretty simple. It looks like this:


$args = array(
 'numberposts' => -1,
 'offset' => 0,
 'orderby' => 'post_date',
 'order' => 'DESC',
 'post_type' => 'post',
 'post_status' => 'publish',
 'year' => $current_year,
 'meta_key' => 'date',
 'meta_value' => $today,
 );

$go = get_posts($args);

With over 3,500 posts in this site, that’s an expensive query. You’ll notice we help narrow that query down a bit by only having WordPress look for posts in the current calendar year. No sense in looking at 2011’s posts for an event in 2013. Even with that reduction, there’s still several hundred posts to sort through.

There are a few ways to reduce the load that query creates. The best way is to cache the results of the query, which will allow us to serve that faster from memory/database then having to make a whole database call with all sorts of parameters.

One way is to use WordPress’ built-in Transients API. It’s a fairly straight-forward system to set, fetch and delete data inside WordPress. This is perfect for things like queries and other rendered data that you’re going to need often.

Setting up and fetching transients is easy.


$transientname = 'hewt';
$cachetime = 60;

if(false === ($go = get_transient($transientname))){
	$go = get_posts($args);
	set_transient($transName,$go,60*$cachetime);
}

That code, in a nutshell, is first checking to see if the transient variable exists and can be fetched. If it can, great, move on. If it can’t or doesn’t exist, run the WordPress query and save the results in the Transient API for an hour. We can start using the $go variable as needed after that.

The trick about WordPress transients is they are stored in the database. But Mike, didn’t you say that having to query the database every time is a performance hit? Yes, but…

Transients are inherently sped up by caching plugins, where normal Options are not. A memcached plugin, for example, would make WordPress store transient values in fast memory instead of in the database. For this reason, transients should be used to store any data that is expected to expire, or which can expire at any time. Transients should also never be assumed to be in the database, since they may not be stored there at all.

The other way to do some as-needed caching is to use a PHP caching solution, such as APC. I’ve talked about caching WordPress queries using APC in this post.

You’d write your PHP code to save to APC the same way.


$transientname = 'hewt';

if($go = apc_fetch($transientname)){}else{
    $go = get_posts($args);
    apc_add($transientname,$go,3600);
}

Same idea here. We’re checking APC to see if the variable is available. If it is, great, move on. If it’s not, run the WordPress query and save the results for the next time the page is loaded.

What’s neat about APC is that instead of writing the values to MySQL, APC stores them in memory, which means its much faster to read data than having to query the disk.

The challenge with APC is that it isn’t installed by default in most LAMP setups. You have to compile it in, and set some initial values. If you’re running WordPress on a shared server, chances are APC isn’t available.

Where I’ve run into challenges with the Transient API is when I need to delete the cache. There isn’t a button to just clear it out. You can do it programmatically using built-in functions, such as

delete_transient( $transientname );

The other catch, WordPress doesn’t delete old cached transients unless you ask for them by name. WPEngine wrote this about Transients API:

But with the WordPress Transients, you get different but still very undesirable behavior. Because the values are written to the database, not a fixed-sized block of RAM, they all stick around. Which means even with the heavily-loaded site, you still have your session data. Awesome!

Or so you thought. Because what isn’t said in the Transient API is what happens when you use unique keys like sessions. And what happens in with the built-in method is that the options table fills up indefinitely! Because: WordPress’s “old data clean up” only operates when you request the key (as we covered earlier). If you just leave the key, it’s left in the options table, forever. There’s no separate process that cleans these up

Stu Miller had this method for cleaning out old transients:

function purge_transients($older_than = '7 days', $safemode = true) {

Clearing out APC can be done in a few ways. You can delete a particular key:

apc_delete($transientname);

APC comes with a web app where you can view data about the cache, status of keys and more. You can log in to the backend and delete a variable there.

You can also do it with PHP outside WordPress by using something like this:

apc_clear_cache();
apc_clear_cache('user');
apc_clear_cache('opcode');

In review, WordPress transients are easy to use, built into every WordPress installation and don’t require additional plugins. Be aware though, that deleting expired and old values can be tricky and require additonal code. APC is fast, PHP-based and easy to access, but may not be available on all installations or server environments.

As I’ve mentioned on Twitter a few times, we recently launched a Buddypress-powered site for our accepted students. We invited a bunch of students and many of them logged in and joined. Accounts were created for each student, and an email was sent to them with their login information.

After a week or so, our awesome Admission team came to me and asked who hadn’t logged in yet. Hmm. Good question. We didn’t have a plugin or anything turned on to track this, so I did some creative coding. Please – if there’s a more efficient way of doing this, let me know.

The first step I did was to query the wp_users table to get the user ID’s of all the users. Something like this:


$query = "SELECT ID,user_email,display_name,user_registered from wp_XXXXXX_users ORDER BY ID";

$result = mysql_query($query);

Now here’s where it gets tricky. The wp_users table in WordPress by default doesn’t tell you the last time someone logged in, just when the account was created. If we want to know when they last did anything, we need to go looking in the wp_usermeta table. In that table is a meta_key field. There are many for each user ID, so we are looking for the one called last_activity. In that field will be the date and time the user last did anything in our site. If they’ve never logged in, they’ll have no activity.

Again, this isn’t efficient, but basically we loop through the IDs we queried before and see if there’s a last_activity value in the meta_key field. If there is, great, that user has logged in. If there isn’t, we can assume they haven’t logged in.

while ($row = mysql_fetch_assoc($result)) {

 $userid = $row['ID'];

 $q = "SELECT * from wp_XXXXXXX_usermeta where user_id = $userid and meta_key = 'last_activity'";

 $r2 = mysql_query($q);

 $num_rows = mysql_num_rows($r2);

 if( mysql_num_rows($r2) > 0){
	//This person has logged in
 }else{
       // Do whatever you need to here. I made a pretty table of user info
	echo "
Not logged in: ".$row['user_email'];
 }

}

If you’re running a site with a few users, this method isn’t too terribly taxing. Our WordPress site, however, has several thousand users so this query is pretty taxing. We don’t run it very often, which is good, because I would not recommend such a inefficient query be run in a production environment.

And since I know you were going to ask, the wp_XXXXXXX_usermeta table is named that way for a purpose. It’s good WordPress security practice to rename your tables from the default titles, because hackers often look for installs that haven’t been changed, and can write their malware to look for tables named in most cases the default, such as wp_usermeta.

The best time to change it is when you’re first installing WordPress, but it is possible to do it after. I’d recommend using a plugin like Chris Weigman’s Better WP Security. It will do the heavy lifting for you. But if you’re feeling adventurous, you can do it manually, by adding this line to your wp-config.php file:

$table_prefix  = 'wp_2f2fss_';

You can make it whatever you like, just make sure you end it with an underscore. The next step is to rename your real database tables. This is slightly more tricky if you don’t have access to the database itself or something my PHPMySQL admin. You can learn more about this process here.

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.