How to delete WordPress spam comments and meta data with phpMyAdmin. A lot of WordPress spam comments and meta data will keep your WordPress database huge, and slows down your site. It’s best to delete spam comments regularly, but if you’re facing hundreds of thousands spam comments, here is how to delete them all in bulk.
Remove WordPress spam comments
In this post I’ll explain how to delete WordPress spam comments, directly in the database and how to delete their meta data. Using the Akismet and WordPress spam comments data, you can start to block comment spammers manually. This sounds as a lot of work but really isn’t.
After blocking comment spammers you can clean their sh?t up. This will keep your WordPress MySQL database small and fast.
Use the following MySQL queries with phpMyAdmin, or the MySQL command line interface, to check spam comments and delete them.
SELECT *
FROM wp_comments
WHERE comment_approved = '0';
Code language: SQL (Structured Query Language) (sql)
DELETE
FROM wp_comments
WHERE comment_approved = '0';
Code language: SQL (Structured Query Language) (sql)
SELECT *
FROM wp_comments
WHERE comment_approved = 'spam';
Code language: SQL (Structured Query Language) (sql)
DELETE
FROM wp_comments
WHERE comment_approved = 'spam';
Code language: SQL (Structured Query Language) (sql)
Clean-up spam meta data
After deleting the spam comments, it is important to clean up the spam meta data. Either from WordPress or Akismet. This meta data is stored in the table wp_commentmeta
. A lot of information is stored there.
First, select the meta data to make sure you don’t accidentally delete other comment information:
SELECT *
FROM wp_commentmeta
WHERE comment_id
NOT IN (
SELECT comment_id
FROM wp_comments
)
Code language: SQL (Structured Query Language) (sql)
If you are satisfied with the outcome, you can delete all spam comment meta data:
DELETE
FROM wp_commentmeta
WHERE comment_id
NOT IN (
SELECT comment_id
FROM wp_comments
)
Code language: SQL (Structured Query Language) (sql)
Do the same for Akismet information. Inspect:
SELECT *
FROM wp_commentmeta
WHERE meta_key
LIKE "%akismet%"
Code language: SQL (Structured Query Language) (sql)
Satisfied with the result? Delete:
DELETE
FROM wp_commentmeta
WHERE meta_key
LIKE "%akismet%"
Code language: SQL (Structured Query Language) (sql)
Protip: Donate $10, 20 or 30 through Paypal (or see my donate page) and support this site. Thank you <3
Thank you for the cleaning tool! Worked perfect! :)