If you are using the default database configuration of Wordpress, then here’s a command line MYSQL query — also works in programs like PHPMyAdmin — to see what the breakdown is of approved, spam and waiting comments:

This will return a query that looks like what’s pictured and highlighted at the top of this post. These are the codes:

0 = pending comments
1 = approved comments
spam = comments you’ve marked as ’spam’ or deleted.

For some strange reason comments aren’t actually deleted in the Wordpress database when marked ‘delete’. I didn’t look in the code to see why this is being done this way, but to me when you delete something that means it actually gets deleted, not saved in the database in the spam bin. Perhaps there is a good reason why the Wordpress team is doing this but you don’t need to keep these deleted/spam comments unless you want to use them to detect spam patterns or abuse.

Assuming you don’t want to keep those comments, here is the code to clean out the spam comments from the wp_comments table in Wordpress:

Voila. You will now have a Wordpress-powered blog clean of spam/deleted comments. The busier your blog, the more these comments will start adding up so might want to set some sort of cron job that wipes out these comments once a week, month, whatever.

Did this post make you go hmm?

 Loading ...

• MySQL queries to sort by most comments by reader in WordPress blog
• Does adding CAPTCHA reduce the number of comments?
• [site news] Slow page loading issues
• [free code] How to import from Pivot 1.2x to Wordpress 1.5x
• How to access MySQL from Windows Command Prompt
• How to deal with (form) comment spammers