Spam Comments and Trashed Posts: Why They Slow the Database and How to Clean Safely

WordPress does not auto-cleanup. Spam comments, trashed posts, and meta rows pointing at posts that no longer exist accumulate for years and balloon wp_comments, wp_commentmeta, wp_posts, and wp_postmeta. On older blogs that often means hundreds of thousands of rows. They drag every SELECT that visits these tables - comment archives, RSS, per-post comment counts, internal search - and inflate backups by 2-5x.

Why this matters

A query filtered on post_status='publish' is not slow on its own, but MySQL still walks the WHERE on every row before it can return matches (when the index is suboptimal). On a site with 1,000 active posts and 50,000 trashed posts, the query touches 51,000 rows instead of 1,000. The penalty shows in feed loops, archive pages, and any comment-counting widget. Backup files grow proportionally - a five-minute backup balloons to 25 minutes, and exporting to staging becomes a chore.

Akismet stats put average spam volume at 200-500 a day after two years of activity. Without recurring cleanup, a two-year-old site carries 150K+ junk rows. Each one occupies disk, gets rebuilt during index maintenance, and rides along in every backup.

How to detect

The diagnostic queries:

-- spam and trashed comments
SELECT comment_approved, COUNT(*) FROM wp_comments
GROUP BY comment_approved;

-- posts by status
SELECT post_status, COUNT(*) FROM wp_posts
GROUP BY post_status;

-- orphan postmeta rows
SELECT COUNT(*) FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL;

Thousands of spam, trash, or orphan commentmeta rows mean there is real cleanup to do.

How to fix

The safest path is the WordPress UI: Comments > Spam > Empty Spam, Comments > Trash > Empty Trash, Posts > Trash > Empty Trash, then repeat for every custom post type (Pages, Products, Forms). With WP-CLI:

wp comment delete $(wp comment list --status=spam --format=ids) --force
wp comment delete $(wp comment list --status=trash --format=ids) --force
wp post delete $(wp post list --post_status=trash --format=ids --post_type=any) --force

For orphan meta:

DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL;

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

Finish with OPTIMIZE TABLE wp_comments, wp_commentmeta, wp_posts, wp_postmeta; to reclaim physical disk space inside InnoDB tablespaces. Without OPTIMIZE, the file size stays the same even though row counts dropped.

For prevention: install Akismet (free for personal sites) or Antispam Bee - either blocks 99% of spam bots without forcing a CAPTCHA. Add define('EMPTY_TRASH_DAYS', 7); in wp-config.php so trash auto-purges after a week.

Common mistakes

Do not lump "pending" comments with spam - those are legitimate awaiting moderation. Review before bulk-deleting. Do not run a bare DELETE on wp_postmeta without joining wp_posts - you risk wiping rows still attached to live posts. Always use the LEFT JOIN ... WHERE p.ID IS NULL pattern. Do not skip OPTIMIZE TABLE; InnoDB does not free space on its own and backups stay heavy. Be careful with revisions: they live with post_status = 'inherit', not trash, so the filtering rules differ from trashed posts.

Verifying the fix

Re-run the count queries - spam and trash buckets should be zero. Check the database footprint with SELECT SUM(data_length+index_length)/1024/1024 FROM information_schema.tables WHERE table_schema=DATABASE();. Expect 30-60% smaller. UpdraftPlus backup file sizes should drop accordingly. Use Query Monitor to compare comment-archive query times before and after - typically 30-50% faster.