Pablowe

Improving WordPress Performance with Basic MySQL Maintenance

If your blog is anything like mine, the vast majority of comments are spam. Most blogs have at least a 50% ratio of spam-to-valid comments, and Pablowe has a 99.4% ratio (which is probably why there are so many Anti-Spam plugins for WordPress).

One of the most oft-executed queries (based on the MySQL general log statistics) is:

SELECT DISTINCT ID, post_title, post_password, comment_ID,
comment_post_ID, comment_author, comment_date_gmt, comment_approved,
comment_type,comment_author_url,
SUBSTRING(comment_content,1,30) AS com_excerpt
FROM comments
LEFT OUTER JOIN posts ON (comments.comment_post_ID = posts.ID)
WHERE comment_approved = '1' AND comment_type = '' AND
post_password = ''
ORDER BY comment_date_gmt DESC
LIMIT 10;

In order to keep this (and other) queries performing well, I put the following script in cron and schedule it to execute weekly:

<?php
// This should match your values in wp_config.php
$table_prefix = '';
$db_host = '';
$db_name = '';
$db_user = '';
$db_password = '';
// How long (in days) before comments are purged
$purge_age = 7; // DEFAULT: One Week
if ( $mysqli = new mysqli( $db_host,$db_user,$db_password,$db_name) ) {
$purge_query = 'DELETE FROM ' . $table_prefix .
'comments WHERE comment_date < DATE_SUB(NOW(), INTERVAL ' .
$purge_age . " DAY) AND comment_approved = 'spam'";
if ( ! $mysqli->query ( $purge_query ) ) {
die ( "Could Not Issue Query: Please check configuration\n" );
}
$mysqli->close ( );
} else {
die ( "Could Not Connect: Please check configuration\n" );
}
?>

Will this make your blog Digg Proof? Not by itself, but it can play an important part in the overall performance and scalability of your blog.