Sometimes, sites can get overloaded with spam comments—thousands per day. Whether this is the case, or you’ve just never emptied out your spam comments on a big site, you can end up with too many to trash. This is due to some restrictions to the amount of data that can be sent along with a request.
You can clean things up with some simple SQL queries—it’s much more efficient than trying to use the WordPress admin area when you need to remove spam comments in bulk.
Before you do anything, get a full backup of your database. Seriously.
Querying the Database
Depending on your hosting environment and your level of expertise, you’ll run these queries
- using phpMyAdmin, in the SQL tab
- using a database management GUI, like Sequel Pro
- using the MySQL command-line tool
To check the output of any of these queries before actually deleting anything, replace DELETE FROM with SELECT * FROM. You’ll see all the rows that will be deleted when you run the real query.
As well, note that these queries assume your database table prefix is wp_. You might need to change it.
Spam Removal Queries
To delete all comments marked as spam, run:
DELETE FROM wp_comments WHERE comment_approved = 'spam';That should clear out your spam comments area in the admin. You should see about as many rows affected as you had spam comments.
Meta Data Cleanup
Even after you delete all those comments, you’ll still be left with all their metadata cluttering your database.
If you use Akismet, you can run this to get rid of everything:
DELETE FROM wp_commentmeta WHERE meta_key LIKE '%akismet%';This query will attempt to delete any comment that no longer exists. It’s a little more complex, so double check the output before deleting—hopefully, the previous query will do the hard work first.
DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);After all is said and done, optimize both the wp_comments and wp_commentmeta tables.
Now, go figure out how to stop that spam from ever getting to you in the first place. 🙂