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

The scenario:

You want to exclude a category from being processed by a post loop, with posts in said category having more than one category¹, keep wordpress db calls to a minimum², no dropping of data after you’ve already hit the db³ and finally provide a set limit of posts.

The quest:

After a quick grep of the codex⁴ and a forum entry, I have managed to reduce the db load every time someone hit’s the landing page by a massive degree.

The answer:

The goal was to reduce the severe performance hit against the db by restricting the query to that which is needed and nothing more.

By default wordpress does not handle category exclusion at all well, particularly when posts are in more than one category and the methods by which one excludes an entry based on category often come down to querying the db for everything, then dropping unwanted items in the loop - which has always struck me as a very slow, db intensive concept.

The result is the following code block that provides only what is needed and nothing else:

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

In the above example we create a new query, for all posts, that are not in category ‘14′, limit the query to 20 hits and then store the results in $posts ready for the usual foreach() used in most post loops.

I’m quite certain further improvements can be made to create an even more efficient db query - but it’s a good start. Help reduce de-forestation, limit greenhouse gases and cut the power bill - use selective queries, today.

1. which takes ‘&cat=-xx’ out of the equation (↩)
2. bye bye &limit=$gdp_of_china (↩)
3. hitting the db with mass queries that get dropped during the loop is just plain bad (↩)
4. I love you codex, even if your search is half broken! (↩)