Removing WordPress Spam Comments In Bulk

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.

Does your comments page look like this?
Does your comments page look like this?

 

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.

Removing WordPress Spam Comments In Bulk

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s