Delete postmeta after delete revisions

• susa38

• August 10, 2017 at 4:31 pm

I’ve 2 custom fields from advanced custom fields.
I normally delete with phpmyadmin the revisions of the post to clean my database.

DELETE FROM wp_posts WHERE post_type = 'revision';

Now I have a very big wp_postmeta table and I’ve made question to see how many postmeta fields do not have relation with any post

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

and the result is total: 256927 fields
And a lot of the are from my custom fields form acf

Some of the adf are emty and other are with content.

1- Are these postmeta from deleted post or from deleted revisions?
2- Is a problem to delete them from my database?
3- Is it possible to delete the post meta fields from acf when I delete the revisions?
4- Is there a way from advanced custom field to delete all the fields that the plugin do not need?
5- Do you have a query to delete all the postmeta that is not needed?

For each item on tthe wp_postmeta table I have 2 fields

For example:

Meta ID Post ID Meta_key Meta_value
28218 8796 my-acf-name
28219 8796 _my-acf-name field_517ea3ab3737
28220 8796 my-acf-name2 this is content
28221 8796 _my-acf-name2 field_517ea3ab3737

6- is correct to delete the 2 fields from each acf items?

• susa38

• August 15, 2017 at 3:54 am

Have anyone deleted the post_meta files from my custom fields that are without post_id?
Is dangerous?
SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL

is there any query to make that?

• John Huebner

• August 15, 2017 at 11:06 am

https://wordpress.org/plugins/advanced-database-cleaner/

• susa38

• August 22, 2017 at 9:57 pm

Could any admin form advanced custom fields comment this post if i’s possible to clean the post_meta table with not problems?
On my first message I comment what is the problem?
I deleted with php my admin the post revisions and now there are a lot of custom fields files on the post_meta table. some of them without post_id relation, other with no content…

• John Huebner

• August 22, 2017 at 10:04 pm

The problem is that you deleted the posts in phpMyAdmin. When you do this it becomes extremely difficult to delete the orphaned post meta values. The link I posted is to a plugin that’s supposed to be able to clean this up.

The only way short of this is to build your own SQL queries and run them through php. The steps involved are

1. Query the database and get a list of all active post IDs
2. Run a delete query on the _postmeta table WHERE post_id NOT IN (post id list)

The developer of ACF does not visit here much, this is a users helping users forum. If you want to get input from the developer then the best chance you have of that is submitting a new support ticket here https://support.advancedcustomfields.com/new-ticket/

• susa38

• August 25, 2017 at 4:52 pm

thanks a lot John.
I have this query to view the postmeta files that do not have any post relation

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

But the query do not show only the advanced custom fields files.
On the next query I have added the acf field to select only these fields

SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL AND pm.meta_key LIKE '%my-custom-field-name%'

And now I would like to know if the query is correct and i can make the delete query without problems and errors on my wordpress after

DELETE FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL AND pm.meta_key LIKE '%my-custom-field-name%'

• John Huebner

• August 25, 2017 at 10:29 pm

Honestly, I can’t say if your query is right or wrong by looking at it. For me, I would back up the database and run it and see what happens. I’m not really an expert on SQL.