wp_posts cleanup: removing 600 dead CPT rows safely

A Dutch agency we work with sent over a staging dump last Thursday with a single line in the message: "600 of these. None of them render anywhere. Can we just nuke them?" The 600 in question were rows in wp_posts with post_type = 'event_legacy', a custom post type registered in 2021, deprecated two years ago, and unregistered from the codebase since the last theme rebuild. The rows were still there. So was the postmeta. So were the term relationships, the revisions, the orphan comments. The shortest path to a clean wp_posts cleanup is not DELETE FROM wp_posts WHERE post_type = 'event_legacy';. That command leaves four other tables holding pointers to rows that no longer exist.

Below is the actual sequence we ran on a copy of the agency's database. It assumes a wp_ table prefix and InnoDB. Adjust both before you paste.

Mapping the damage before you DELETE

The first job is to confirm the scope. Not "how many wp_posts rows" but "how many rows in every table that points back to those wp_posts rows." On a typical WordPress install that means five tables: wp_posts itself, wp_postmeta, wp_term_relationships, wp_comments, and wp_commentmeta. Revisions and autosaves live inside wp_posts with post_type = 'revision' and a post_parent pointing at the parent row, so they need a separate sweep.

SELECT post_type, post_status, COUNT(*) AS n
FROM wp_posts
WHERE post_type = 'event_legacy'
GROUP BY post_type, post_status;

SELECT COUNT(*) AS meta_rows
FROM wp_postmeta
WHERE post_id IN (SELECT ID FROM wp_posts WHERE post_type = 'event_legacy');

SELECT COUNT(*) AS term_links
FROM wp_term_relationships
WHERE object_id IN (SELECT ID FROM wp_posts WHERE post_type = 'event_legacy');

SELECT COUNT(*) AS revisions
FROM wp_posts
WHERE post_type = 'revision'
  AND post_parent IN (SELECT ID FROM wp_posts WHERE post_type = 'event_legacy');

SELECT COUNT(*) AS comments
FROM wp_comments
WHERE comment_post_ID IN (SELECT ID FROM wp_posts WHERE post_type = 'event_legacy');

On the agency's site the answers came back: 612 in wp_posts, 7,344 in wp_postmeta (twelve meta keys average per row, including the bloat from ACF), 1,820 in wp_term_relationships across three taxonomies that had also been deprecated, 0 comments, and 488 revisions. Total rows touched: 10,264. Without the cascading deletes, 9,652 of those would have become orphans the moment wp_posts dropped its rows.

Also worth a look before you delete anything: dump a handful of representative meta keys.

SELECT meta_key, COUNT(*) AS n
FROM wp_postmeta
WHERE post_id IN (SELECT ID FROM wp_posts WHERE post_type = 'event_legacy')
GROUP BY meta_key
ORDER BY n DESC
LIMIT 30;

Sometimes a CPT was retired by name but the meta keys were repurposed for a newer type. If you see _event_start rows attached only to event_legacy posts, fine. If you see them attached to both event_legacy and event, the meta key is still in use and you only want to drop the rows whose post_id falls inside the doomed set.

The four tables that silently break

WordPress does not use foreign-key constraints. The $wpdb abstraction treats every relationship as a soft pointer and trusts the application to keep them honest. That trust is fine until you bypass the application and DELETE in SQL. The four pointers that get left dangling are:

• wp_postmeta.post_id points at wp_posts.ID. Orphan meta rows are harmless for the front end but they show up in every SELECT the admin runs over postmeta and they accumulate forever.
• wp_term_relationships.object_id points at wp_posts.ID. Orphan rows here inflate wp_term_taxonomy.count on every term, which is what powers category and tag counts in the admin. The numbers stay wrong until wp_update_term_count_now() runs or you fix them by hand.
• wp_comments.comment_post_ID points at wp_posts.ID. Orphan comments are invisible in the admin queue but they keep showing up in any plugin that does its own comment query.
• wp_posts.post_parent on revisions, autosaves, and attachments points at the parent row. Orphan revisions cannot be cleaned up from the admin because there is no parent screen to open.

The order of deletes matters because the lookups in step one depend on the parent rows still being present. Delete wp_posts first and the IN (SELECT ID FROM wp_posts ...) subqueries in the dependent deletes return an empty set, which leaves everything else orphaned. The fix is to invert the order: dependents first, parents last.

The cleanup, in order

Before any of this runs, take a hot backup. mysqldump --single-transaction --quick wp_database > backup.sql on a transactional InnoDB schema gives you a consistent snapshot without locking writes. Verify the file is non-zero and contains the table headers you expect. Then open a transaction so the whole sequence is atomic.

START TRANSACTION;

-- 1. Cache the doomed IDs so every step uses the same set
CREATE TEMPORARY TABLE doomed_ids AS
SELECT ID FROM wp_posts WHERE post_type = 'event_legacy';

-- 2. Revisions and autosaves first (children of the parent rows)
DELETE FROM wp_posts
WHERE post_type IN ('revision', 'auto-draft')
  AND post_parent IN (SELECT ID FROM doomed_ids);

-- 3. Postmeta
DELETE FROM wp_postmeta
WHERE post_id IN (SELECT ID FROM doomed_ids);

-- 4. Term relationships (record the term_taxonomy_ids first, we need them)
CREATE TEMPORARY TABLE touched_tt AS
SELECT DISTINCT term_taxonomy_id
FROM wp_term_relationships
WHERE object_id IN (SELECT ID FROM doomed_ids);

DELETE FROM wp_term_relationships
WHERE object_id IN (SELECT ID FROM doomed_ids);

-- 5. Commentmeta then comments
DELETE FROM wp_commentmeta
WHERE comment_id IN (
  SELECT comment_ID FROM wp_comments
  WHERE comment_post_ID IN (SELECT ID FROM doomed_ids)
);

DELETE FROM wp_comments
WHERE comment_post_ID IN (SELECT ID FROM doomed_ids);

-- 6. The parent rows
DELETE FROM wp_posts
WHERE ID IN (SELECT ID FROM doomed_ids);

-- 7. Recount the term taxonomy counts we just invalidated
UPDATE wp_term_taxonomy tt
SET count = (
  SELECT COUNT(*) FROM wp_term_relationships tr
  WHERE tr.term_taxonomy_id = tt.term_taxonomy_id
)
WHERE tt.term_taxonomy_id IN (SELECT term_taxonomy_id FROM touched_tt);

COMMIT;

Two things are worth noticing. First, the temp table. MySQL re-evaluates correlated subqueries on every row of a DELETE, so passing the same SELECT ID FROM wp_posts WHERE post_type = 'event_legacy' into five statements would be five full scans against a table that is shrinking as you go. The temp table caches the set once. Second, the recount in step seven. Skipping it leaves wp_term_taxonomy.count inflated by however many term relationships you just deleted. The category widget will keep showing "Events (612)" until a post in that category is saved through the admin.

If the table is too big to delete in one shot

On the agency's database the parent table held 1.4 million rows and the cleanup ran in under a second. On a 60 million row wp_posts the same DELETE will hold a long transaction, blow up the binlog, and make replication lag visible to anyone watching. Batch it. The pattern that works without changing the logic above is to add a LIMIT to step six and loop until ROW_COUNT() returns zero, regenerating doomed_ids at the top of each batch.

Verifying nothing else regressed

Run the same five count queries from the audit again. They should return zero across the board, except for wp_posts which should return zero rows of post_type = 'event_legacy' and the original total of every other type. Then run two sanity checks the audit did not catch:

-- Orphan postmeta with no parent row anywhere
SELECT COUNT(*) FROM wp_postmeta pm
LEFT JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL;

-- Orphan term_relationships with no parent row
SELECT COUNT(*) FROM wp_term_relationships tr
LEFT JOIN wp_posts p ON p.ID = tr.object_id
WHERE p.ID IS NULL;

If either of those returns a non-zero number, you have orphans from some earlier event, not this one. Worth knowing about, not worth panicking. A separate cleanup pass with the same pattern handles it. The WordPress reference for wp_delete_post() lists the exact set of side effects the application layer normally handles, which is a useful checklist for any future custom-type retirement.

Then: flush the object cache (wp cache flush if WP-CLI is on the box), regenerate the sitemap (Yoast, RankMath, and the core sitemap all cache the post-type list), and rebuild any persistent search index. ElasticPress, Algolia, and Relevanssi all keep their own copies of wp_posts rows and will keep returning the deleted ones in search until reindexed. Finally, drop a 410 Gone rule in .htaccess if the old CPT permalinks were ever indexed by Google, so the crawler stops asking for them.

The receipt

When we built Pier we hit this exact pattern on three of the first ten legacy sites we opened, and the order of operations was always the thing nobody had written down. So we wired the audit-then-temp-table-then-cascade sequence into the MySQL editor as a saved query you can run against any post_type, and every step lands a row in version history so the undo is one click if something downstream complains.

The smallest thing to do today: run the five audit queries against your own staging copy and see how many orphan rows you already have from cleanups that were not done in the right order.