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.

Start Slide Show with PicLens Lite