I discovered a couple of days ago that I had 118,000 comments on this blog. The vast majority were, of course, spam. In fact as best as I can tell I had only 2 genuine comments, one from my mate Barry and another from a guy in China.
I installed Akismet to do spam filtering, but this did not deal with the existing spam mountain. I made various attempts to scale the spam peak, and recover the odd real comment which may have been lost there. But this resulted in me maxing out the CPU on the webserver, and my host suspending the website.
After a courteous email exchange JustHost graciously reinstated my account, and I was left feeling somewhat nervous. After googling for a while the only apparent viable option was to delete the comments directly in the database.
After backing up the database and looking at the database schema, I chose the following sql delete commands:
delete from wp_commentmeta limit 1000; delete from wp_comments limit 1000;
The “limit 1000”, means only 1000 records are deleted at a time, so the commands had to be run repeatedly. First I deleted from wp_commentmeta, then wp_comments. I left 15 seconds between each delete, so as not to hit the CPU too heavily and incur the wrath of my host.
All the comments are gone. Barry and the guy in China — sorry.