About 6 months ago I had a serious spam attack here in my blog, and received more than 60 000 spam comments in a few days. For that reason I decided to turn off the option to comment on blogs, but I never managed to get rid of all the comments that was already in my system. The WordPress admin system only allows for deleting 20 comments at a time, so that didn’t seem like the way to go.

This morning I finally decided to figure out how to solve this, and found a great description for mass deleting comments directly from mySQL. This can all be done from phpMyAdmin which is (usually) installed on the server where the mySQL is running.

  1. Backup: in phpMyAdmin select your database(s), click the export tab, check SQL, click save file, click go. This will result in a file being downloaded to your computer.
  2. Delete: in the overview click on the table with your comments (the default is wp\_comments). Then go to the SQL tab, and type in
    3. - DELETE FROM wp\_comments WHERE comment\_approved = 0
  3. Optimize: in the overview select the comments table and choose “optimize table” from the drop down box.
This elegantly removed all my spam comments. Now I just need to get my WordPress updated and see if I dare opening for comments again.

alt text