I’ve encountered a few WordPress blogs that had been bombarded with spam comments (some over 200k comments – taking several gigs of db space). Usually these comments accumulated over a few months or years (many using Akismet) – but if you’ve tried to delete thousands of comments through the WordPress admin, you might have noticed it taking very long and timing out.

Having these comments exist in your WordPress site only increases the size of the database, causing backups and migrations / upgrades to take longer. Unless you have plans to review thousands of comments (if so, you probably have too much time on your hands) you should be able to delete these in my opinion. The fastest option to remove the comments that I’ve found is to delete them from the database side (MySql).

Before I show you the simple sql I run, please make a backup of your database. Please!

If you haven’t made a backup of your database, why did you keep reading? Go back and make a backup!

Now that you’re good, the below three queries delete spam comments, comment meta data and meta data from all comments. Run these in the order listed.

--1. Removes the comments marked as spam
delete from wp_comments where comment_approved='spam';
--2. Removes the meta data for comments already deleted (give you space in your db back)
delete FROM wp_commentmeta where comment_id NOT IN ( SELECT comment_id FROM wp_comments );
--3. Potential approved meta data not needed (probably won’t be much of this)
delete from wp_commentmeta where meta_key LIKE '%akismet%';

As a side note, I use HeidiSQL to connect to my WordPress MySql db’s. I found HeidiSQL to be fast and reliable.