Linux and PHP web application support and development (Bromsgrove, UK)
A WordPress site we maintain had one page (warning: some of the pages/images may offend) displaying a large number of posts (they all happen to be galleries) using the nextgen-gallery plugin.
The page in question, on each load, would perform a query on posts within a specified category, and display them inline. It then used a jQuery listnav plugin to provide ‘paging’ to the data to make it manageable for the end user (showing A/B/C/…. links at the top).
The problem with this is that the page’s load time increases with the number of posts – although it is fairly responsive once loaded. For a busy webserver, it’s not ideal for the server to be tied up for some period of time generating html and transmitting data to the end user. As the server already makes good use of caching plugins / tools (memcached/w3 total cache so on) it seemed a good idea to use these within the resultant ‘fix’.
The initial code looked a bit like :
$posts = new WP_Query("cat=999&showposts=-1&orderby=title&order=ASC");
while ($posts->have_posts()) : $posts->the_post();
// Do stuff to print out the post (thumbnail and markup etc)
}
So, as above, you can see it pulling back all posts within a category (pictures) and looping to render the HTML for all gallery items at once.
Suffice to say, the page was slow to load.
We changed the page to use a URL query parameter to indicate which page the user was on (namely: http://site.com/picture-gallery/?q=something ) so we could limit the posts shown to be only those starting with the given parameter; for the sake of simplicity the query parameter is restricted to a single character – so posts are grouped alphabetically.
However we couldn’t see any easy way to use the WP_Query class to achieve this (as we effectively want to do the equivalent of an SQL LIKE match on the first letter of the post’s title).
So, we moved to using the $wpdb object directly, using something like :
$q = $_GET['q'];
// perform validation check against $q - should be
// only an alphabetical character or _ (for posts starting with a number)
$sql = "
SELECT {$wpdb->posts}.* FROM {$wpdb->posts}
LEFT JOIN {$wpdb->term_relationships} ON
({$wpdb->posts}.ID = {$wpdb->term_relationships}.object_id)
LEFT JOIN {$wpdb->term_taxonomy} ON
({$wpdb->term_relationships}.term_taxonomy_id = {$wpdb->term_taxonomy}.term_taxonomy_id)
WHERE ({$wpdb->term_taxonomy}.taxonomy = 'category'
AND {$wpdb->term_taxonomy}.term_id = 9849 )
AND
{$wpdb->posts}.post_status = 'publish' AND
{$wpdb->posts}.post_type = 'post' AND
";
if($q != '_') {
$sql .= "{$wpdb->posts}.post_title like '$q%'";
}
else {
$sql .= "{$wpdb->posts}.post_title REGEXP '^[0-9]{1}'";
}
$sql .= " ORDER BY {$wpdb->posts}.post_title ASC";
$posts = $wpdb->get_results($sql, OBJECT);
The above is all very well, but we need to have some sort of caching in place on the page, to avoid unnecessary database read traffic. The $wpdb object doesn’t provide any caching built in (it appears to do simple caching within the request itself – which is probably just a case of it doing a MySQL buffered query).
So, to add caching to the query, we can use the wp_cache_* functions as follows.
We chose to use a hash of the SQL as cache key – as this should be unique for the data (the query will change depending on what page(s) is requested) and so on.
posts = wp_cache_get(md5($sql), 'thegallery');
if($posts === false) {
$posts = $wpdb->get_results($sql, OBJECT);
wp_cache_add(md5($sql), $posts, 'thegallery');
}
The cache key has been md5()’ed to try and give some sensible randomisation/uniqueness – so hopefully we have a sensible distribution of keys within the cache (if we are using a file based cache it can be a problem if there are too many files within one directory). It also ensures the cache key cannot contain any annoying characters (e.g. quote marks or whatever).
Footnote/update – further investigation has shown that the w3-total-cache plugin replaces the $wpdb global with a database object which has built in caching. The above should still be of use/relevance to those not using w3t though.
And that’s pretty much it.
‹ Sponsoring the 2011 PHPNW Conference Xerte Online Toolkits ›