Removing Old Entries From Database If You Have List of Field ID's

• timmy28x

• March 3, 2019 at 6:53 am

I know this has been discussed a bunch and people have tried to provide solutions. The only solution I saw that seemed to work for removing old orphaned rows from all the tables of the database required you to have had the sense to prefix all of your Field ID’s, which I didn’t.

Here’s what’s going on. I’m converting meta fields on posts over to custom blocks, which gets all the data into the post content table, which is preferable and one cool thing Gutenberg got right.

I will have a list of all of the field ID’s that I’ll no longer be using. Between Yoast and ACF, my database is getting silly large.

Since I’ll have that list of field ID’s and all the new ones will be named differently, is there any method to hunt down all of this orphaned stuff in the database and get rid of it using this list of field ID’s?

• John Huebner

• March 4, 2019 at 5:07 am

The main issue with wholesale deleting of data is that even if you are very careful and you’re pretty sure the field names are not used anywhere else, there can almost never be 100% certainty that they are not.

There can also be an issue with sub fields because the only way to deleted these would be using a “LIKE” query. For example, you have a repeater (this includes flex fields and group fields) named “REPEATER_FIELD” you would need to delete everything that was “LIKE” “REPEATER_FILED_%”. This is where the certainty of field names not being reused can drop further.

In addition to the above, for every ACF field name there is also the field key reference that also needs to be dealt with. For every field there is another field that starts with an underscore. “FIELD_NAME” has a corresponding “_FIELD_NAME”.

That being said, you can use phpMyAdmin and do delete queries on the tables deleting anything where the meta_key matches or is like the field you want to delete.

With that said, the size of a database will only severely effect performance of a site when doing “LIKE” queries on non-indexed columns. There can also be a small performance hit when doing other queries on non-indexed columns, but if you are never doing a query based on these old meta_keys (field names), the extra data in the DB should not effect performance. For example:

'SELECT * 
 FROM wp_postmeta 
 WHERE meta_key = "field_name"
   AND meta_value' = "some value"'

Will not effect performance because “meta_key” is indexed and “meta_value” will never be looked at in rows where the meta_key does not match.

It may bother some people that all this unused data is in the database, but any idea that the size of a DB alone will affect the performance of a site, as far as I’m aware, is incorrect. Issues arise because the queries on the data and how they are constructed and not due to the data that is being queried.

• timmy28x

• June 27, 2019 at 10:04 am

So all of my posts are now Gutenberg, and all old fields are now custom blocks, and all old data are now in the blocks and in the post content verses meta data.

I still have my old Field Groups enabled and not-deleted.

The field names are exactly these:

• intro-summaries
• tldr-box
• features-hero
• features-alt
• item-1-affiliate-link
• item-1-brand
• item-1-image-url
• item-1-bottom-line
• item-1-pros
• item-1-cons

The “item-1” ones have variants that say “item-2” and “item-3” as well.

There’s almost zero way that WordPress or myself have any other meta-keys that match these right? I’ve never used normal custom fields. Only ACF, and only Yoast. I doubt Yoast is using these.

So what should I do here?

1) My understanding is that if I delete the Field Groups, the data for these fields will still exist in the posts. Would they then be considered orphaned and a database cleaner plugin find them?

2) Alternatively, I understand that there’s something about going through your posts and deleting all these content in these fields, setting them to be blank. Does that then remove these entries in the database or are they still there but blank? If this will delete them then I’ll happily grind it out and make them blank in the post edit screen.

3) The third option is deleting the Field Groups and then running a SQL query on the database.

Can anyone guide me here to the surefire way to get rid of these entries that doesn’t involve me nuking my site? (I’d take a backup first of course).

• John Huebner

• June 27, 2019 at 9:21 pm

3 is the only way that your values will be deleted.

Delete from all meta tables where meta_key = “{$field_name}” OR = “_{$field_name}”

• timmy28x

• June 28, 2019 at 5:59 am

Thanks, John.

For anyone following along, this is the SQL query to get this done if your fields are simple fields (not repeaters or flexible):

DELETE FROM wp_postmeta WHERE wp_postmeta.meta_key = "custom_field_name" OR wp_postmeta.meta_key = "_custom_field_name"