Deleting Massive Amounts of Comments in Wordpress

If you are like me and neglected your wordpress blog for a ridiculous amount of time you probably have a ridiculous amount of comments awaiting moderation. Unfortunately, wordpress only displays 20 comments per page so processing 10,000+ spam comments will take forever! And clicking on “Check For Spam” doesn’t seem to be able to handle massive amounts of pending comments. Therefore, after spending a couple of hours deleting comments 20 at a time I decided to find a better way.The following only works if you have access to your server or can log into the database with a mysql client:

First, you should back up the wp_comments table in case anything goes wrong:

# mysqldump -u [username] -p --single-transaction [database name] wp_comments > wp_comments.sql

Then you can log into the database and delete pending comments on mass:

# mysql -u [username] -p [database name]
mysql>delete from wp_comments where comment_approved = 0 and comment_content like '%viagra%';

Notice the “viagra” part? Just do a quick audit of your pending comments and look for obvious spam content. Stick any string of characters from the spam in between the “%” symbols and you will make quick work of thousands of spam comments. Just keep repeating the delete statement. The goal is to get it down to a reasonable number so that you can get Akismet to do the rest by clicking on “Check For Spam”.

Note that I did this with version 2.7.1 of Wordpress.