Remove all spam WordPress comments
Posted on May 8th, 2010 in EN | 1 Comment »
After not cleaning up spam comments for a while, I reached more than a 18000 spam comments. At this point WordPress built-in “Empty Spam” function is no helpful, as it only succeeds to delete about 300 comments before the connection to web server is timed out.
Googling immediately brings the way to remove all the spam comments directly from MySQL:
DELETE FROM wp_comments WHERE comment_approved = '0';
Good trick, but it does not work anymore.
SELECT DISTINCT comment_approved FROM wp_comments;
returns 1, spam. So, the proper query would be
DELETE FROM wp_comments WHERE comment_approved = 'spam';
Before running the DELETE query, you may run the following to make sure you get exactly the same number of span comments in db and in WordPress admin console:
SELECT COUNT(*) FROM wp_comments WHERE comment_approved = 'spam';
Hooray, no spam anymore!
One Response
It’s worth cleaning up the wp_commentmeta table too. I do this:
delete from wp_comments where comment_approved = ‘spam’;
delete from wp_commentmeta where comment_id not in (select comment_id from wp_comments);
There’s probably smarter/more efficient ways to do this, but it works okay if you don’t have millions of non-spam comments.