Or, to be fair, MySQL 3.x is rubbish. But you knew that already.
Most of the blogs on the Ozblogistan network use BLC. Recently, Larvatus Prodeo came on board and expected it to work for them too. But it simply hung without giving much in the way of error message. I pottered around with it at the time without much success.
Today it occurred to me that the problem is the way BLC works. BLC aims at being compatible with MySQL 3.x. Among other things, this means giving up subqueries and having piss-poor SELECT DISTINCT.
So BLC issues this beauty of a query:
SELECT comment_post_ID, post_title
FROM (wp_comments LEFT JOIN wp_posts ON (comment_post_ID = ID))
WHERE comment_approved = '1'
AND wp_posts.post_status='publish'
AND comment_type'pingback'
AND comment_type'trackback'
ORDER BY comment_date DESC;
This query pulls out the post ID and post_title for every approved, published comment in the database. On a site like Larvatus Prodeo, the results run to ~165k lines. BLC pulls these results into PHP as objects, one for each line; then it merges down the results to determine which posts have comments.
As you can imagine, this is memory intensive. Very memory intensive. And it’s a bit brutal on the CPU too, especially when garbage collection occurs. The upshot is that on Larvatus Prodeo, BLC exceeds the memory limits for PHP, and that for other sites, it adds about between 600 and 1200 milliseconds of processing time. Not cool.
Naturally, I’m running MySQL 5.x on my server, so SELECT DISTINCT works ‘as advertised’. So I’ve implemented a super high tech optimisation on BLC:
SELECT DISTINCT comment_post_ID, post_title
FROM (wp_comments LEFT JOIN wp_posts ON (comment_post_ID = ID))
WHERE comment_approved = '1'
AND wp_posts.post_status='publish'
AND comment_type'pingback'
AND comment_type'trackback'
ORDER BY comment_date DESC;
While this query is slower than the original, it basically returns only the list of posts with comments on them. On Larvatus Prodeo, this is a much more manageable ~2.5k lines of results. The reduction in PHP runtime completely overshadows the increase in query time; and besides, the smaller result sets don’t clog up the MySQL query cache.
So there you have it: a one-word way to dramatically improve the performance of Brian’s Latest Comments on MySQL 5.x-backed Wordpress.
Update: Two additional optimisations are worthy. First, add a LIMIT clause to your copy of BLC along the lines of:
$posts = $wpdb->get_results("SELECT DISTINCT
comment_post_ID, post_title
FROM ($wpdb->comments LEFT JOIN $wpdb->posts ON (comment_post_ID = ID))
WHERE comment_approved = '1'
AND $wpdb->posts.post_status='publish'
$ping
ORDER BY comment_date DESC
LIMIT $num_posts;");
Second, the SELECT DISTINCT hammers the tables much harder, so add indexes for the relevant fields:
CREATE INDEX post_status_index ON wp_posts(post_status);
CREATE INDEX comment_date_index ON wp_comments(comment_date);
CREATE INDEX comment_type_index ON wp_comments(comment_type);
CREATE INDEX comment_date_approved_index ON wp_comments(comment_date_gmt);
CREATE INDEX post_title_index ON wp_posts(post_title(50));
Update 2: The query above doesn’t order the results according to the time of the latest comments; it winds up ordering by posting date. Not what my users want. Try this instead:
$posts = $wpdb->get_results("SELECT comment_post_ID, post_title, max(comment_date) AS max_date
FROM ($wpdb->comments LEFT JOIN $wpdb->posts ON (comment_post_ID = ID))
WHERE comment_approved = '1'
AND $wpdb->posts.post_status='publish'
$ping
GROUP BY post_title
ORDER BY max_date DESC
LIMIT $num_posts;");
Update 3: Nope. Performance is still atrocious. I’ll revisit this in a few weeks when I’ve settled in Darwin.
