0

I'm looking to cleanup wp_postmeta. I've found dozens of duplicate entries.

I've spend about 8 hours looking through topics and trying code and plugins to no avail.

The most useful article on the subject I found is this.

However the article is from 5 years ago and I'm not sure that the suggested code leaves at least 1 entry from each duplicate. Also the plugin that is suggested (Cleanup Duplicate Meta) is exactly what I need, but it hasn't been updated for 7 years. The plugin tells me there are over a million duplicates, but when I click to clean it up, nothing happens.

Can someone confirm that

DELETE FROM `wp_postmeta` WHERE `meta_id` NOT IN (SELECT * FROM (SELECT MAX(`pm`.`meta_id`) FROM `wp_postmeta` pm GROUP BY `pm`.`post_id`, `pm`.`meta_key`) x)

will delete the duplicates but leave at least 1 entry of each?

enter image description here

Improve this question

1 Answer 1

Reset to default
0

This should do it:

DELETE t1 FROM wp_postmeta t1, wp_postmeta t2 
WHERE t1.meta_id > t2.meta_id 
AND t1.meta_key = t2.meta_key 
AND t1.post_id = t2.post_id
Improve this answer
2
  • I had to have my hosting provider do the operation as the connection to the server was timing out when I tried it. The query took 4h to complete, but at least so far it looks to be a resounding success. Can't find anything broken with the website. Thank you so much. Went from 1.5mil to only 60k entries!!! Now it's just a question of wtf was creating all of this bloat.
    – Daniel
    Commented Jul 14, 2022 at 15:58
  • @Daniel Please select my answer as correct if it helped you :)
    – Himad
    Commented Jul 15, 2022 at 1:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.