Page Text: February 27, 2021
How to Use a Query to Bulk Delete WordPress Comments
My WordPress database backups tend to get rather large over time. The reason for this is because of the sheer number of spam comments my blog receives. At the time of writing this I have 3,764 comments marked as spam. All of which I received over the past few weeks.
You might be thinking… Aren’t there plugins that can help prevent spam comments? Well, sure there are.
And aren’t there plugins that will delete spam comments in WordPress for you? Yep, there are those too.
However, the point of this blog post is to show how we can delete these comments with some fairly simple queries. I’m assuming you already know how to use phpMyAdmin or a MySQL Client to run queries on your database. By the way, it’s a good idea to make a database backup beforehand in case things don’t go as expected.
Alright, enough talking. Let’s see the queries.
SELECT Queries
Before we run the DELETE queries, let’s run these SELECT queries to get a preview of the records that will be deleted.
wp_commentmeta table
SELECT cm.* FROM wp_commentmeta cm INNER JOIN wp_comments c ON cm.comment_id = c.comment_ID WHERE c.comment_approved = 'spam'
wp_comments table
SELECT c.* FROM wp_comments c WHERE c.comment_approved = 'spam'
It’s important to note that we are working with two different tables here. The wp_commentmeta table stores optional information about each comment. The wp_comments table stores the actual comment itself.
DELETE Queries
After previewing the records we want to remove, you are ready to run these DELETE queries. The order here matters. Run the DELETE query on the wp_commentmeta first. This is so we can prevent orphaned records.
wp_commentmeta table
DELETE cm FROM wp_commentmeta cm INNER JOIN wp_comments c ON cm.comment_id = c.comment_ID WHERE c.comment_approved = 'spam'
wp_comments table
DELETE c FROM wp_comments c WHERE c.comment_approved = 'spam'
Your tables may have a different prefix. Just change ‘wp_’ to whatever your prefix may be.
Comment Types
If you want to target different comment types you can do so by looking for different comment_approved values.
comment_approved = '0' /* pending comments */ comment_approved = '1' /* approved comments */ comment_approved = 'spam' /* spam comments */ comment_approved = 'trash' /* trashed comments */
Hope this helps. That’s all for now.