Skip to main content

Clean-up WordPress spam comments and meta data

How to clean up 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

Clean-up 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';
DELETE
  FROM wp_comments
  WHERE comment_approved = '0';
SELECT *
  FROM wp_comments
  WHERE comment_approved = 'spam';
DELETE
  FROM wp_comments
  WHERE comment_approved = 'spam';

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.

I thought you might find this interesting:   WordPress HyperDB is an advanced database class that supports replication

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
  )

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
    )

Do the same for Akismet information. Inspect:

SELECT *
  FROM wp_commentmeta
  WHERE meta_key
  LIKE "%akismet%"

Satisfied with the result? Delete:

DELETE
  FROM wp_commentmeta
  WHERE meta_key
  LIKE "%akismet%"

Sources:

Change Akismet spam delete interval

The Akismet plugin automatically deletes spam entries from your WordPress database. It does so, however, after 15 days, which is quite a long time. Use the following PHP snippet in your THEME functions.php to change the delete interval to, let’s say, 2 days:

<?php
add_filter( 'akismet_delete_comment_interval', 'saotn_set_akismet_delete_interval' );
function saotn_set_akismet_delete_interval() {
  return 2;
}

Disable comments in WordPress

After reading this article, I can imagine you’d like to disable the comment system in WordPress completely. That makes your life a whole lot easier… So I’ve created a guide: Read how to disable WordPress comments.

Jan Reilink

My name is Jan. I am not a hacker, coder, developer, programmer or guru. I am merely a system administrator, doing my daily thing at Vevida in the Netherlands. With over 15 years of experience, my specialties include Windows Server, IIS, Linux (CentOS, Debian), security, PHP, websites & optimization.

One thought to “Clean-up WordPress spam comments and meta data”

Hi! Join the discussion, leave a reply!