SQL Query to delete orphans wp_postmeta

25 000 posts (and each has only 1 custom field)

Does every post have 1 custom field that has the same key?

Postmeta has a lot of records for locking posts and other stuff, so if your posts have one custom field with the same key, say the key is ‘my_custom_key’ then this SQL in phpMyAdmin could work:

DELETE FROM wp_postmeta WHERE wp_postmeta.meta_key != 'my_custom_key'

Remember Page Templates attached to Pages have records in this table. You might want to look for other exceptions before proceeding.

Or course, backup your database before attempting anything like this.

This plugin might do what you want: https://www.ads-software.com/extend/plugins/optimize-db/ .

You can do a plugin search with the kewords ‘optimize database’ to find more plugins like this one.

vtxyzzy – that plugin looks like it optimizes tables, but don’t see any DELETE statements.

Thanks for response :

Here is the content of wp-postmeta (query : SELECT meta_key, COUNT( meta_key ) FROM wp_postmeta GROUP BY meta_key)

embed 1
thumbnail 715625
title 2
_edit_last 95
_edit_lock 95
_wp_attached_file 1
_wp_attachment_metadata 1
_wp_old_slug 3

So I have 715 625 custom field thumbnail but only 25 000 posts with 1 custom field thumbnail = 690 000 more. So I would like to identify thumbnail which don’t match with existing post-id (from wp-posts) and delete theses from the wp-postmeta table.
Of course I need to keep the 25 000 good custom field data from wp-postmeta.

Well this will get all records in postmeta that don’t have corresponding records in the posts table.

SELECT *
FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
WHERE wp.ID IS NULL

Works great. Thanks for your help

@michaelh

Thanks as I see that query helps identify the redundate postmeta. However, what would be the mySQL command to delete those entries? Thanks

Delete for Michaels query (untested).

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

An alternative select..

SELECT * FROM wp_postmeta
WHERE NOT EXISTS (
	SELECT * FROM wp_posts
	WHERE wp_postmeta.post_id = wp_posts.ID
);

As delete.

DELETE wp_postmeta FROM wp_postmeta
WHERE NOT EXISTS (
	SELECT * FROM wp_posts
	WHERE wp_postmeta.post_id = wp_posts.ID
)

Any of these queries should be preceded by a backup of important data, we will not take responsibility if something goes wrong, so please be sure your important data is backed up first.

I had the same issue (orphan metadata from deleted posts) and ran Mark’s version first:

DELETE wp_postmeta FROM wp_postmeta
WHERE NOT EXISTS (
	SELECT * FROM wp_posts
	WHERE wp_postmeta.post_id = wp_posts.ID
)

which deleted ALL of my postsmeta table! After restoring that table, I ran Michael’s version:

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

which worked like a charm. Thanks Michael!