Sometimes while developing a new plugin you have to do lots of database work including creating posts, editing posts, and deleting posts. All of these actions affect the post meta data table and if you aren't careful with how to manage the post meta data you'll have thousands and thousands of rows of data. By deleting orphaned post meta data (meta data belonging to posts which no longer exist) you immediately remove thousands of records that weren't doing anything.
SQL Query
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DELETE pm | |
| FROM wp_postmeta pm | |
| LEFT JOIN wp_posts wp ON wp.ID = pm.post_id | |
| WHERE wp.ID IS NULL |
There are plenty of other solutions but beware that some of them might delete your entire table! Make sure you back everything up before you execute any of these.
[…] to Patrick Rauland for submitting this useful […]
Thank you so much. This is great tip 🙂
A year later and I am saying thanks!
Thank you as well for pushing this out there. I too needed to clean up post_meta after dropping 1.2M rows of pseudo-garbage from wp_posts.
You’re so welcome! 🙂
Thanks for this code, it worked great!
Hey, is there any danger in running this code?
Any SQL query is risky. You could easily mess up some data in your database. Before running any SQL query read through it to make sure it’s doing what you want it to do. In this case it’s pretty straight forward. If you want to be extra cautious create a DB backup before running the query.
I hope that helps! 🙂
I am wondering if you have an idea how to remove all the _postmeta data from WordPress/Woocommerce pertaining to FORMER user’s orders. I am going to be exporting my sql db file to work with another person and so I deleted all users and all orders from Woocommerce, incl. from the Trash. However, to make sure I looked through the database file I exported after the deletions and I noticed that much, if not all, the user shipping, billing, etc. data is STILL in the sql file in the _postmeta table with the fields
(
meta_id,post_id,meta_key,meta_value)that I ‘thought I deleted’ on the front end. I will see if I can pull some sql tricks but wondered if you had an idea already. Very odd (poor design) this stuff is not removed upon the front end deletion.
Thanks in advance!
Sorry – not sure off the top of my head how to delete all of this data. I’d reach out to someone like Codeable to write you a quick snippet.
Where Should I put the code? and how does it know which things are suppose to be there? Thanks.
This is a SQL statement. You’ll need to open up your database (usually via PHP MyAdmin or some sort of admin interface) and then run a SQL statement, sometimes called a SQL command.
is this code woocommerce “safe” ?
I’ve used it for two years without problems. 🙂
Thanks buddy it helped me to remove about 1000 orphans users!
Like a boss!
Want to know were to put this line of code. Someone can help please?
I run this code but the resul was “zero deleted”. But I know there are data entries belonging to dead plugins. What if I delete them individually once I spotted they have that old plugin’s prefix? Is it safe?
If you don’t plan on using that plugin anymore there’s no reason why you can’t delete the meta data manually.
While I like to keep things clean removing meta data manually won’t give you any significant performance boosts. This is helpful when you have thousands of pieces of meta data that can be deleted.
After use sql query my site got 500 error then i am back to backup db where i made miss take? Also i have 19758 orphaned postmeta in my database :/
Finally bookmarked this page after searching for it every time I need to reset some data in my local development environment. 😛
Thanks Patrick, you’re the best!
This command cleaned up my db quite a bit. Took a LONG time to finish though. Thanks :D!
Now I learned the tips of Delete Orphaned Post Meta Data in WordPress, Thanks for sharing!
Thanks for this tutorial man, you helped me a lot!
This trick is awesome, my databse size 234 mb and delete some colums last size is 155 mb. thanks man!
Thanks. That’s a query you never remember but always need 😀
Is there a way to delete in batches?
I have over 10 million orphaned items and the server keeps craping out after 60 seconds (WP Engine).
Is there any reason why post meta data would be intentionally orphaned?
Or used in some way that would break site functionality if removed?
A plugin that my developer created was responsible for the massive amount of orphans. They created a script to delete them in batches of 100,000 which is working. Unfortunately, I can’t see it or I would share it here. Thanks for the quick response!
Thank you helped a lot
Thanks!
perfect. thanks ! incase if not work for someone please check the prefix “wp_”
as some cpanel installs make different prefix.
Thanks for this code, it’s so useful !
I do this with help from wordpress database cleaner, is very useful for operator without database skill
This is the first step of two. Step two is this:
ALTER TABLE
wp_postmetaADD CONSTRAINTwp_post_idFOREIGN KEY (post_id) REFERENCESwp_posts(ID) ON DELETE CASCADE ON UPDATE CASCADE;This will fix it forever. You delte a post, mysql removes meta data.
Thanks for great help . It’s really important to delete it to keep my blog healthy .