Bulk deletion of WordPress spam comments

On a long-running website, spam comments can accumulate in the tens of thousands. The WordPress admin interface is manageable up to a few thousand rows, but becomes unresponsive beyond that. By following the steps below, you can safely clean up the comment tables of websites hosted on cPanel and configure the system to prevent spam from accumulating again.

We tested these commands in October 2026 on an aWh cPanel test account running WordPress 7.1.

1. How much spam is there?

In the admin interface, the tabs on the Comments page (Pending, Approved, Spam, Trash) show the number of entries. You can also query this in phpMyAdmin (cPanel Databases section) on the SQL tab:

SELECT comment_approved, COUNT(*) AS db
FROM 1vL7334_comments
GROUP BY comment_approved;

The table prefix (1vL7334_ in the example) is random on websites installed using WP Toolkit. You can find your own using the wp db prefix command, or view it in the list on the left-hand side of phpMyAdmin. The values in the comment_approved column are:

Value	Meaning	Can they be deleted in bulk?
`spam`	Marked as spam (the ‘Junk’ tab)	Yes
`trash`	Moved to the bin	Yes
`0`	Awaiting moderation (Pending)	No, there may also be genuine comments amongst them
`1`	Approved, will appear on the site	No
`post-trashed`	This entry was moved to the rubbish bin along with the post	Only available with the post

2. Backup

Rows deleted using SQL cannot be undone, so you should first perform a backup of the database using the ‘Export’ tab in phpMyAdmin or the mysqldump command. The article on using WP-CLI explains how to use the command without a password.

cd ~/public_html
P=$(wp db prefix); DB=$(wp config get DB_NAME)
mysqldump --single-transaction --no-tablespaces "$DB" > ~/hozzaszolasok-elott-$(date +%F).sql
tail -1 ~/hozzaszolasok-elott-$(date +%F).sql

The last line should read -- Dump completed on …

3. Select the deletion method

Less spam: the admin interface

In the ‘Comments’ › ‘Spam’ tab, the ‘Empty Spam Folder’ button works well for up to a few thousand lines. According to our tests, it deleted 1,000 lines in 8 seconds, but with 20,000 lines, it had not finished even after four minutes and had only deleted roughly a third of them by then. If the button stops due to a timeout, do not click it again; instead, use one of the methods described below.

Lots of spam via SSH: WP-CLI

On plans with SSH access (cP Premium, cP Pro, cP Pro 100), WP-CLI is the most convenient option, as it deletes not only the posts but also their associated metadata. Work in small batches, taking breaks in between:

cd ~/public_html
while IDS=$(wp comment list --status=spam --number=500 --format=ids) && [ -n "$IDS" ]; do
  wp comment delete $IDS --force | tail -1
  sleep 1
done

The --force option skips the rubbish bin. For posts in the rubbish bin, run the same cycle with the --status=trash switch.

A lot of spam: in batches via SQL

For tens of thousands of rows, SQL is the fastest: in our tests, 13,000 rows were deleted in three seconds.

while :; do
  N=$(mysql "$DB" -N -e "DELETE FROM ${P}comments WHERE comment_approved = 'spam' LIMIT 5000; SELECT ROW_COUNT();")
  echo "törölve: $N"
  [ "$N" -eq 0 ] && break
  sleep 1
done

Without using SSH, run this in the SQL tab of phpMyAdmin, using your own table prefix, and repeat until the number of affected rows drops to zero:

DELETE FROM 1vL7334_comments WHERE comment_approved = 'spam' LIMIT 5000;

The SQL delete command does not affect the commentmeta table, so you can proceed to the next step.

4. Deleting orphaned metadata

Spam filter plugins write data to the commentmeta table for every comment. If you delete comments using SQL, these rows remain. In our test, after deleting 13,000 comments, 13,000 orphaned rows remained. Their number is:

SELECT COUNT(*)
FROM 1vL7334_commentmeta cm
LEFT JOIN 1vL7334_comments c ON c.comment_ID = cm.comment_id
WHERE c.comment_ID IS NULL;

To delete them:

DELETE cm FROM 1vL7334_commentmeta cm
LEFT JOIN 1vL7334_comments c ON c.comment_ID = cm.comment_id
WHERE c.comment_ID IS NULL;

If you deleted it using WP-CLI, you can skip this step, but it is worth running the verification query.

5. Spam that has got past the filter

Any spam messages that the filter has not recognised will appear on the site as approved comments. You can find them by the linked web address or the sender’s name. Always start by using SELECT to see what the query would return:

SELECT comment_ID, comment_author, comment_author_url, comment_content
FROM 1vL7334_comments
WHERE comment_approved = '1'
  AND comment_author_url LIKE '%gyanus-oldal.hu%';

You can search for recurring senders as follows:

SELECT comment_author_email, comment_author_IP, COUNT(*) AS db
FROM 1vL7334_comments
GROUP BY comment_author_email, comment_author_IP
HAVING db > 5
ORDER BY db DESC;

If the results are indeed spam, flag them in the admin interface or run the query in the format DELETE FROM 1vL7334_comments WHERE …. After doing the latter, carry out steps 4 and 6.

6. Correcting the number of comments

WordPress stores the number of comments per post separately. Deleting spam and the trash folder does not affect this; only deleting approved comments via SQL will. If the wrong number is displayed on the page:

wp comment recount $(wp post list --post_type=any --format=ids)

The wp comment recount command requires you to specify the post IDs; without them, it will only display the usage information. Without SSH, in phpMyAdmin:

UPDATE 1vL7334_posts p
SET p.comment_count = (
  SELECT COUNT(*) FROM 1vL7334_comments c
  WHERE c.comment_post_ID = p.ID AND c.comment_approved = '1'
)
WHERE p.post_type IN ('post', 'page');

7. Table compression

The space occupied by the deleted rows within the tables is freed up, but the files on the disk will not become smaller until the tables are rebuilt:

OPTIMIZE TABLE 1vL7334_comments, 1vL7334_commentmeta;

The comment Table does not support optimize, doing recreate + analyze instead is normal; the important thing is that the status in the last row is OK. The operation locks the tables for a short time, so run it during a period of low traffic. If the website uses an object cache (such as Redist), clear that as well.

How can you prevent this?

On the Settings › Chat page:

Before a comment is published: enable the** ‘Comments must be manually approved’** setting, or at least ensure that the comment author has previously had a comment approved. This prevents comments from bots from appearing on the site.
Further comment settings: Automatically closing the comments section on old posts eliminates the majority of spam targets. The requirement to register and log in to post comments is even stricter.
Comment moderation: you can add recurring phrases and titles to the ‘Prohibited comment keywords’ field.
If you do not require comments: disable the ‘Allow people to post comments on new entries’ setting. This only applies to new entries; older ones must be closed separately.

Manual moderation does not stop spam; it merely hides it, so it is worth using a spam filter plugin as well. Akismet, which comes with WordPress, requests an API key and sends comment data to an external service for analysis; this must be stated in the privacy policy. Requests from bots still put a strain on hosting resources even if their comments do not appear: if they are arriving in large numbers from a single address, block that IP address.

Further articles on database maintenance cover the bloated wp_postmeta table and automatically loaded data in the wp_options table, as well as our blog post on WordPress database optimisation.