I was recently working on a clients site, and that site has accumulated over 8000+ spam comments. Unfortunately the site did not have an anti-spam plugin activated, so the comments were being held in the moderation queue making comments on the site unusable.

Akismet to the Rescue

I thought I could install and activate akismet, then run the re-check queue function. In essense this procedure re-runs the akismet check against all unmoderated comments, and any that match spam profiles are binned.

Due to the large number of spam messages, the re-check queue function timed out and would not perform the check.

Getting Down and Dirty

The only thing for me to do was to run some sql commands directly against the database and delete the unmoderated comments tbat way.

Because I don’t do things easily, I did not have access to the backend database, I would have to find a solution inside of WordPress.

My Solutions

First thing was to install a plugin which allows me to run comamnds directly against the wqordpress database, the Plugin I like to use for this purpose is wp-dbmanager, you can download this plugin from http://wordpress.org/extend/plugins/wp-dbmanager/

Once installed there are various options to maintain your database, optimise table, but the main function I wanted was the ability to run a query, there is an option to do this.

The Query

I ran the following query against my clients database:

DELETE from `wp_comments` WHERE `comment_approved` = ‘0’;

This is a powerful command, make sure you understand it before you go ahead, what it is saying is delete all rows from the table wp-comments where comment_approved = 0 or where a comment is not moderated. This will not delete approved comments, only comment sitting in the moderation queue.

The Result

The 8000+ spam comments were gone, akismet is installed and my client now has the confidence to re-open comments if they so choose. A neat little fix.

Once you know about wp-dbmanager there are a hundred and one admin tasks you can perform, but please use with caution and after a backup because you can also irreparably harm your site, that was my polite way of saying if you don’t know SQL commands don’t mess 🙂