General

Remove comment spam from your WordPress database

by Bastien 3 Comment

I have been getting a lot of comment spam on this blog. I've just removed over 20.000 comments from the database. Apparently WordPress still stores the comment if you mark it as spam. I haven't looked in the code if it uses this for some clever techniques to detect future spam. Right now I just wanted to clean up the database because I'm going to upgrade to WordPress 2.

The following website has listed two MySQL commands. One with which you can count the amount of spam and one to remove all the spam-comments:
Count the amount of comment types:
select count(comment_ID), comment_approved from wp_comments group by comment_approved;
Remove all spam comments:
delete from wp_comments where comment_approved = 'spam';

Source: http://www.makeyougohmm.com/20061207/4025/

3 comments

Jenny

Interesting…maybe I should write a php file that we can schedule (with cron) to execute each 12 or 24 hours. It's a lot more easier than to log in to WordPress and delete the spam comments manually.

Bastien

Hello Jenny,

Indeed, you could then schedule it to run for example every month, depending on the amount of comments you get. However I'm not sure if the comment-spam is use by WordPress to train its spam-filters.

Kind regards,
Bastien

Periko

Thank you a lot, this helped me to delete 10200 lines in my db !!!