Bulk-Delete Spam Comments and Spam Users from the WordPress Database

This article is a localized edition of the Japanese original. Read the Japanese original

What this article covers

Spam comments stay in wp_comments and wp_commentmeta until you delete them. Once tens of thousands pile up, the comment list and backups slow down. This article shows how to bulk-delete spam comments and spam user registrations safely, starting with the simplest method.

Back up the database before you start.

wp db export before-spam-cleanup.sql

Table names here use the wp_ prefix. If yours differs, check it with wp db prefix and adjust the table names in the SQL.

Check the counts and sizes first

# Comment counts by status (approved, pending, spam, trash, etc.)
wp comment count

# Spam count only
wp comment list --status=spam --format=count

# Size of each table
wp db size --tables

In wp_comments, the comment_approved column is 1 for approved, 0 for pending, spam for spam and trash for trash.

wp db query "SELECT comment_approved, COUNT(*) AS cnt FROM wp_comments GROUP BY comment_approved ORDER BY cnt DESC;"

Method 1: Delete from the admin screen

Up to a few thousand comments, the admin screen is enough.

1. Open Comments → Spam.
2. Click Empty Spam above the list. Every comment classified as spam is deleted without going through the trash.
3. On the Trash tab, Empty Trash works the same way.

With very large numbers the request can time out. In that case, use WP-CLI as shown next.

Method 2: Bulk-delete with WP-CLI

wp comment delete --force deletes through WordPress's wp_delete_comment(), so the comment's wp_commentmeta rows go with it and post comment counts are updated.

# Delete spam 1,000 at a time until none are left
while true; do
  ids=$(wp comment list --status=spam --format=ids --number=1000)
  [ -z "$ids" ] && break
  wp comment delete $ids --force
done

# Delete trashed comments the same way
while true; do
  ids=$(wp comment list --status=trash --format=ids --number=1000)
  [ -z "$ids" ] && break
  wp comment delete $ids --force
done

Passing every ID at once can exceed the command-line length limit, so the IDs are processed in batches of 1,000.

Deleting only old spam

To leave time to rescue false positives, delete only spam older than a set period. wp comment list has no date filter argument, so use date_query in get_comments() via wp eval.

wp eval '
$comments = get_comments( array(
    "status"     => "spam",
    "date_query" => array( array( "before" => "30 days ago" ) ),
    "number"     => 1000,
    "fields"     => "ids",
) );
foreach ( $comments as $id ) {
    wp_delete_comment( $id, true );
}
echo count( $comments ) . " deleted\n";
'

Each run deletes up to 1,000 comments. Repeat until it reports 0.

About pending comments

Pending comments (--status=hold) include legitimate ones. Before bulk-deleting them, review them with wp comment list --status=hold --fields=comment_ID,comment_author,comment_date --number=50.

Method 3: Delete directly with SQL

If you have hundreds of thousands and even WP-CLI is slow, you can delete directly with SQL. This bypasses WordPress (hooks and post comment count updates), so use the methods above when they are enough. Spam and trashed comments are not counted in post comment counts, so deleting only those does not make the counts wrong.

-- Delete spam
DELETE FROM wp_comments WHERE comment_approved = 'spam';

-- Delete trash
DELETE FROM wp_comments WHERE comment_approved = 'trash';

-- Delete commentmeta whose parent comment is gone
DELETE cm FROM wp_commentmeta cm
LEFT JOIN wp_comments c ON c.comment_ID = cm.comment_id
WHERE c.comment_ID IS NULL;

Writing it as NOT IN (SELECT ...) is slower on large tables than a LEFT JOIN.

When running through wp db query, pass one statement at a time.

wp db query "DELETE FROM wp_comments WHERE comment_approved = 'spam';"

Reclaiming space after deletion

Deleting many rows does not shrink InnoDB table files right away. Rebuilding the table shrinks the file.

wp db query "OPTIMIZE TABLE wp_comments, wp_commentmeta;"

InnoDB reports "Table does not support optimize, doing recreate + analyze instead". That means the table was rebuilt; it is not an error. Writes to the table may wait during the rebuild, so run it at a quiet time.

Deleting spam user accounts

If you accept registrations, spam accounts can pile up. Deleting users with SQL leaves their wp_usermeta behind, so delete them with WP-CLI.

First export the list to CSV and review it.

wp user list --role=subscriber --fields=ID,user_login,user_email,user_registered --format=csv > subscribers.csv

Delete the IDs you decided to remove (replace the example IDs).

wp user delete 123 124 125 --yes

If you delete without --reassign, the user's posts are deleted too. Subscribers normally have no posts, but when deleting users with roles such as Author, hand their posts over with --reassign=<ID-of-user-to-keep>.

To remove subscribers who registered before a certain number of days ago, collect their IDs like this. Do not use it on sites with legitimate subscribers, such as membership sites.

wp db query "SELECT u.ID FROM wp_users u
JOIN wp_usermeta m ON m.user_id = u.ID AND m.meta_key = 'wp_capabilities'
WHERE m.meta_value LIKE '%\"subscriber\"%'
  AND u.user_registered < DATE_SUB(NOW(), INTERVAL 90 DAY);" --skip-column-names

Review the IDs before passing them to wp user delete. The wp_ in wp_capabilities also follows your table prefix.

Preventing it from coming back

• Akismet: Akismet cleans up comments it flagged as spam in a daily scheduled task and deletes spam older than 15 days (the akismet_delete_comment_interval filter in Akismet's class.akismet.php, default 15). With Akismet active, spam does not accumulate indefinitely.
• Trash: WordPress core's scheduled task deletes trashed comments older than EMPTY_TRASH_DAYS (default 30 days).
• User registration: If you do not need registrations, uncheck "Anyone can register" under Settings → General.
• Not using comments: Unchecking "Allow people to submit comments on new posts" under Settings → Discussion stops comments on posts created from then on. Existing posts keep their own setting, so change them with bulk edit if needed.

Automatic deletion without Akismet

If you use another anti-spam tool and comments marked as spam keep piling up, WP-Cron can delete old spam on a schedule. This example goes in wp-content/mu-plugins/spam-cleanup.php.

<?php
/**
 * Plugin Name: Spam Cleanup
 * Description: Deletes spam comments older than 30 days every day.
 */

add_action( 'init', function (): void {
    if ( ! wp_next_scheduled( 'myplugin_spam_cleanup' ) ) {
        wp_schedule_event( time() + HOUR_IN_SECONDS, 'daily', 'myplugin_spam_cleanup' );
    }
} );

add_action( 'myplugin_spam_cleanup', function (): void {
    $ids = get_comments( array(
        'status'     => 'spam',
        'date_query' => array( array( 'before' => '30 days ago' ) ),
        'number'     => 500,
        'fields'     => 'ids',
    ) );

    foreach ( $ids as $id ) {
        wp_delete_comment( $id, true );
    }
} );

Each run is capped at 500 so that WP-Cron, which runs on page requests, does not take too long. Check the scheduled event with wp cron event list.

Summary

• Back up with wp db export first and check the numbers with wp comment count.
• For small amounts use Empty Spam in the admin; for large amounts delete 1,000 at a time with WP-CLI.
• If you delete with SQL, also remove orphaned wp_commentmeta and reclaim space with OPTIMIZE TABLE if needed.
• Delete spam users with wp user delete so no wp_usermeta is left behind.
• Keep it from recurring with Akismet's automatic deletion and by closing registration.

For managing users with WP-CLI, see also Using the WP-CLI user commands.

Questions about WordPress? The publisher of this site is based in Japan and replies in Japanese. Contact (Japanese)