WordPress comments got out of hand?
The result: 90,000+ comments which led to every comment spammer in the world lining up to add a comment! This almost brought the web server to a complete halt as the amount of connections to the database and the bandwidth that had been used. The below picture will show you just how much of a bandwidth hog this site became from leaving comments open…
For well over a fortnight this website was using around 4GB+ of bandwidth a day! Look at the difference after the comments were disabled and removed. Back to about 30MB a day of bandwidth after discovering the issue on the 30th of November.
Anyone who has ever gone through this will tell you once the damage has been done, it’s too late to run over to Askimet to save you.
So how do you get rid of bulk comments? Answer: PHPMyAdmin.
Now most of your spam comments will have links in the comment, so once you log into PHPMyAdmin you can navigate to the offending database and run this SQL query:
delete from wp_comments where comment_content like ‘%http://%’;
I also would run another query for the people commenting and adding their website URL. Warning this could delete non-spam comments but you have to weigh up what is more important, the time it would manually take to remove them vs the non-spam comments lost.
delete from wp_comments where comment_author_url like ‘%http://%’;
And if you are finding that the bulk of the spam left over is from hotmail email accounts you could run:
delete from wp_comments where comment_author_email like ‘%hotmail%’;
It can be a bit of trial and error to whittle away the spam comments but it will restore your blog from being targeted by spammers.
Hope this helps someone who gets themselves in this situation 😉