smackfoo.com | I find your lack of faith disturbing.

There are times where the standard wordpress loop, is a simple, effective and well documented solution to generate a page, or a series of posts. It safely hides all the mySQL query language away in reusable, modular PHP expressions and can be called at the drop of a hat, as needed.

However, it’s downfall is that it’s based on grabbing n amount of posts based on the options set — which then requires expressions run against the results to further prune out unwanted data, such as excluding a post based on a category, or date. This, then, creates a scenario whereby there is a sizeable performance hit as the wordpress db is hit with, for example, a request for 10 entries, of which some may be thrown out during the_post() loop.

With an increasing amount of meta data being stored in the db, such as custom tags, video, image and audio information, it all adds up to a lot of background processing that is, essentially, often not destined to be presented if entries are dropped or skipped during page render. Much of that data isn’t indexed¹, which further increases the time and load against your host’s db server.

There is, however, a good deal of scope in creating custom mySQL queries to bypass the standard post loop code and directly interegate the db for only that which is needed.

Firstly, lets check out the ‘usual’ loop block that most people will be, at least in part, familiar with:

<?php if ( have_posts() ) : while ( have_posts() ) : the_post(); ?>

Now, this is by no means the end-of-the-story, but simply where the loop is set up, ready to then process entries.

Now, an example code block, taken from this very blog, as an illustration of what can be done²:

<?php
$today = current_time('mysql', 1);
$myrecentposts = $wpdb->get_results(
"SELECT DISTINCT * FROM $wpdb->posts, $wpdb->post2cat ".
"WHERE post_date_gmt < '$today' ".
"AND (post_status = 'publish') ".
"AND $wpdb->posts.ID = $wpdb->post2cat.post_id ".
"AND $wpdb->post2cat.category_id != '113' ".
"GROUP BY $wpdb->posts.ID ".
"ORDER BY post_date DESC ".
"LIMIT 3 ");
if ($myrecentposts) : foreach ($myrecentposts as $post) :
setup_postdata($post); ?>

At first glance, it looks somewhat more complex and as though a great deal more is going on than the kind of loop code your used to seeing. This, in part, is actually true. The reason, primarily, is how we formulate the request for data to begin with. We’re not grabbing 10 entries, then rejecting anything that doesn’t fit the mould — rather we are stipulating, via a direct request, exactly what we want.. and no more.

Stepping through the code, we start by a select that uses the following rules:

1. Select distinct (i.e. unique) entries
2. where the post date is less than now (i.e. anything except future dates)
3. that has been saved and published (i.e. ignore drafts, etc)
4. perform a sanity check against the categories (i.e. make sure we have a post and category match)
5. ignore entries that are in a specific category (i.e. skip unwanted posts)
6. on reaching the third entry, stop
7. .. and finally return the results in an array, in this case $myrecentposts

This results in only 3 entries pulled from the db, based on a set of highly configurable rules, which are then returned in an easily accessed query. The advantages don’t stop there — as one can continue to use the same loop template tags as you may already be familiar with.

Now, one would think the above would result in a massive increase in response times.. which is to a certain extent, quite true. However, as I have mentioned, the wp db isn’t overly well indexed — added to that I perform a SELECT *³ which is often not recognised as the most efficient method.

With up-to-date indexing within post and category tables the select sequence could be further improved efficiency wise, however based on current performance statistics, it’s quite a bit quicker returning results, than returning 10 entries which are then pruned further.

I’m quite sure some of the more savvy mySQL users out there would also have some ideas to further improve efficiencies and reduce db load and if you happen to be of such kin, please do feel free to comment.

Questions? :)

1. .. at all, let alone efficiently. (↩)
2. .. we’ll cover it in more detail shortly (↩)
3. .. if anyone has a better SELECT option, please do let me know! (↩)

I have a problem.
After fruitlessly seeking a particular cd through my assorted collection of cd and dvd wallets this morning I came to the frustrating conclusion that I cannot find jack shit.
A quick trip down to the local PC store & I’ve rocked back with a
particularly swanky looking cd and dvd case.

I […]

As has been noted by a few people now, smackfoo has experienced some issues issues recently with mySQL db corruption and severe post duplication.
This is yet another lengthy post, so if you’re reading this via your RSS reader of choice, you might want to check out the full post. :)
It’s amazing how quickly one […]