---
title: "Clean Up WordPress Comment Spam with phpMyAdmin"
description: "Bulk-deleting spam from the WordPress Comments screen fails once you are dealing with tens of thousands of rows. This guide covers the SQL approach: which queries to run, how to find spam the filter..."
url: https://www.inmotionhosting.com/support/edu/wordpress/remove-wordpress-spam-comments-phpmyadmin/
date: 2026-08-03
modified: 2026-08-03
author: "Carrie Smaha"
categories: ["WordPress Tutorials"]
type: post
lang: en
---

# Clean Up WordPress Comment Spam with phpMyAdmin

Bulk-deleting spam from the WordPress Comments screen fails once you are dealing with tens of thousands of rows. This guide covers the SQL approach: which queries to run, how to find spam the filter missed, and the two cleanup steps most tutorials leave out.

The fastest cleanup is one query:

```
DELETE FROM wp_comments WHERE comment_approved = 'spam';
```

That clears everything Akismet or your moderation queue already flagged. Spam that slipped through needs targeted searching, and once the rows are gone you still have two problems to fix: orphaned metadata in `wp_commentmeta`, and post comment counts that no longer match the database.

## Important Warnings Before You Begin

There is no undo in SQL. Three things first:

- **Export the database.** Use the phpMyAdmin **Export** tab with the Quick method and SQL format, or cPanel’s Backup Wizard for very large databases.
- **Verify your table prefix.** Examples here use `wp_`. Check the `$table_prefix` line in `wp-config.php`, which also lists your database name under `DB_NAME`.
- **Run a SELECT first.** Every DELETE below has a matching SELECT. Run that, review what comes back, then swap in the DELETE.

For a few hundred comments, the admin **Comments > Spam > Empty Spam** button is enough. It starts failing in the low thousands, when the request exceeds `max_execution_time` or `memory_limit` and dies without deleting anything. That is the point where phpMyAdmin earns its place.

## Which Tables and Statuses Control WordPress Comments?

Two tables matter. `wp_comments` holds the comments. `wp_commentmeta` holds metadata attached to them, which is where anti-spam plugins write their scoring data.

The columns you will filter on are `comment_post_ID`, `comment_author`, `comment_author_email`, `comment_author_url`, `comment_author_IP`, `comment_content`, and `comment_approved`.

That last one does the heavy lifting. It is a varchar column, not a boolean, and it holds five values set through [`wp_set_comment_status()`](https://developer.wordpress.org/reference/functions/wp_set_comment_status/) and [`wp_trash_post_comments()`](https://developer.wordpress.org/reference/functions/wp_trash_post_comments/):

| Value | Meaning | Safe to bulk-delete |
| --- | --- | --- |
| `1` | Approved and live | No |
| `0` | Pending moderation | No |
| `spam` | Flagged as spam | Yes |
| `trash` | Trashed | Yes |
| `post-trashed` | Parent post is trashed | Only with the post |

Status `0` catches a lot of people out. It is the moderation queue, not a spam bucket, so deleting it removes legitimate comments waiting on your approval.

## How To Delete Every Comment Already Marked as Spam

Check the volume first:

```
SELECT comment_approved, COUNT(*) AS total
FROM wp_comments
GROUP BY comment_approved;
```

Then clear the two safe statuses:

```
DELETE FROM wp_comments WHERE comment_approved = 'spam';
DELETE FROM wp_comments WHERE comment_approved = 'trash';
```

On a site that has been collecting spam for years, this alone can remove a six-figure row count.

## How To Find Spam the Filter Never Flagged

Approved spam is the harder problem. It sits live on your posts with a plausible name and a link in the website field.

```
SELECT comment_ID, comment_author, comment_author_url, comment_content
FROM wp_comments
WHERE comment_author LIKE '%payday%'
   OR comment_author_url LIKE '%bit.ly%';
```

The `%` character matches any sequence, so this catches both “payday loans” and “quick payday.” Review the results, then rerun with `DELETE FROM wp_comments WHERE` and the same conditions. The website field is usually more productive than the name, and `comment_author_IP` works the same way for a single abusive source.

phpMyAdmin’s **Search** tab does this through a form if you prefer clicking. The SQL is worth learning anyway, since the [phpMyAdmin interface](https://www.inmotionhosting.com/support/server/databases/manage-databases-cpanel-phpmyadmin/) changes between releases while the queries do not.

## How To Spot Repeat Spammers With a Grouping Query

Bots reuse identities, so grouping surfaces them without you guessing keywords:

```
SELECT comment_author, comment_author_email, COUNT(*) AS total
FROM wp_comments
GROUP BY comment_author, comment_author_email
HAVING COUNT(*) > 5
ORDER BY total DESC;
```

Note that `> 5` returns authors with six or more. Use `>= 5` if you want five and up. Grouping on name and email together separates a genuine repeat commenter from a bot cycling through addresses. Swap in `comment_author_IP` or `comment_post_ID` to find a single source or a single post under attack, then check `comment_content` before deleting anything.

## Why Does wp_commentmeta Need Cleaning Too?

Deleting from `wp_comments` does not touch `wp_commentmeta`. Anti-spam plugins write a row every time they evaluate a comment, and those rows survive the parent comment. On heavily spammed sites the meta table often ends up larger than the comments table.

```
DELETE cm FROM wp_commentmeta cm
LEFT JOIN wp_comments c ON c.comment_ID = cm.comment_id
WHERE c.comment_ID IS NULL;
```

The JOIN form performs better than a `NOT IN` subquery on large tables. We cover this in more depth in [cleaning up old comment meta data in WordPress](/support/edu/wordpress/cleaning-up-old-comment-meta-data-in-wordpress/).

Deleted rows do not release disk space on their own. `OPTIMIZE TABLE wp_comments, wp_commentmeta;` reclaims it, but [MySQL maps this to a full table rebuild on InnoDB](https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html), so run it during low traffic.

## Why Are Comment Counts Wrong After the Cleanup?

WordPress caches an approved comment total in the `comment_count` column of `wp_posts`. [`wp_update_comment_count_now()`](https://developer.wordpress.org/reference/functions/wp_update_comment_count_now/) recalculates it by counting rows where `comment_approved = '1'`, and direct SQL bypasses that function.

This only matters if you deleted approved comments. Removing `spam` or `trash` rows leaves the count untouched. If yours are off:

```
UPDATE wp_posts p
SET p.comment_count = (
  SELECT COUNT(*) FROM wp_comments c
  WHERE c.comment_post_ID = p.ID AND c.comment_approved = '1'
);
```

WP-CLI has a purpose-built equivalent, [`wp comment recount`](https://developer.wordpress.org/cli/commands/comment/recount/). If the site runs Redis or Memcached, flush the object cache afterward or the admin will keep serving stale counts.

## How To Keep Spam From Filling the Table Again

Under **Settings > Discussion**, require manual approval or a previously approved comment, enable automatic comment closing on posts older than 30 days, and add recurring spam terms to **Disallowed Comment Keys**. Keep an anti-spam plugin active so new spam lands in the `spam` status instead of going live.

Bots posting directly to `wp-comments-post.php` also consume CPU on every attempt. If that traffic is pushing your account against its resource limits, [VPS Hosting for WordPress](/vps-hosting/wordpress) gives you dedicated CPU, memory, and root access for firewall-level filtering. Contact [our 24/7 support team](/contact) if exports time out or you are unsure which database belongs to which site.

## Summary of Recommended Workflow

1. **Export the database** and confirm the file downloaded.
2. **Verify the table prefix** in `wp-config.php`.
3. **Audit statuses** with the `GROUP BY comment_approved` query.
4. **Delete `spam` and `trash` rows.** Leave status `0` alone.
5. **Search for unflagged spam** with `LIKE` patterns, running the `SELECT` before the `DELETE`.
6. **Group by author and email** to catch repeat offenders, verifying content first.
7. **Clear orphaned `wp_commentmeta`** with the `LEFT JOIN` delete.
8. **Resync `comment_count`** only if you deleted approved comments.
9. **Run `OPTIMIZE TABLE`** during low traffic, then flush the object cache if you use one.
10. **Tighten Settings > Discussion** so you are not repeating this next quarter.