Finding, Deleting and Cleaning Orphaned Post Meta in WordPress

Published

NOTE: Please remember to backup your database before doing any SQL level deletes.

Over time, the wp_postmeta table can get littered with a number of records. These linked meta data rows are not always removed when posts from the wp_posts table are deleted. While WordPress has well optimised the wp_postmeta table, it might be wise to clean it from time to time. I had over 250,000 orphan records after 4 years of running a business website – mostly due to events and ACF plugins!

[sql]

— List all orphan rows from wp_postmeta
SELECT * FROM wp_postmeta
LEFT JOIN wp_posts ON wp_posts.ID = wp_postmeta.post_id
WHERE wp_posts.ID IS NULL;

— Delete all orphan rows from wp_postmeta
DELETE wp_postmeta FROM wp_postmeta
LEFT JOIN wp_posts ON wp_posts.ID = wp_postmeta.post_id
WHERE wp_posts.ID IS NULL;

[/sql]

I hope the above helps.