How to: Clean your WordPress database from all the empty Yoast WordPress SEO meta data
How to: Clean your WordPress database from all the empty Yoast WordPress SEO meta data
So as I was working in moving a site from one installation to another using the WordPress export/import utility I noticed something: There was so much data going around that the server was erroring out. I opened the 30mb XML export file (which is ridiculous for only 300 posts) and found myself with lots of duplicate post meta data including lots of empty entries from Yoast WordPress SEO. I proceeded to manually delete duplicates the best I could with regular expressions but found myself in need of a better way to keep my database lean.
I developed some SQL queries to address the empty values I wanted to remove from the database but as of the latest release of Yoast WP SEO they offered the functionality of not persisting empty values and a method to clean the database in a future release. The author also released a list of SQL queries that are more complete than mine (after all, he does know all the meta data he persists while I was just going through the db seeing which empties one were there. So with no further due below is his list of queries to clean your database. Keep in mind you are going to have to change your table name to match your WP prefix and Blog ID if you use multisite:
DELETE FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_meta-robots' AND meta_value = 'index,follow'; DELETE FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_meta-robots-noindex' AND meta_value = '0'; DELETE FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_meta-robots-nofollow' AND meta_value = '0'; DELETE FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_meta-robots-adv' AND meta_value = 'none'; DELETE FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_meta-robots-adv' AND meta_value = ''; DELETE FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_sitemap-prio' AND meta_value = '-'; DELETE FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_sitemap-include' AND meta_value = '-'; DELETE FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_opengraph-description' AND meta_value = ''; DELETE FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_google-plus-description' AND meta_value = ''; DELETE FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_title' AND meta_value = ''; DELETE FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_metadesc' AND meta_value = ''; DELETE FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_metakeywords' AND meta_value = ''; DELETE FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_focuskw' AND meta_value = ''; DELETE FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_bctitle' AND meta_value = ''; DELETE FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_canonical' AND meta_value = ''; DELETE FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_redirect' AND meta_value = ''; DELETE FROM wp_postmeta WHERE meta_key = '_wpseo_video_meta' AND meta_value = ''; DELETE FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_video_meta' AND meta_value = 'none'; DELETE FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_linkdex' AND meta_value = '0'; DELETE FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_newssitemap-original' AND meta_value = ''; DELETE FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_newssitemap-stocktickers' AND meta_value = ''; DELETE FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_sitemap-html-include' AND meta_value = '-'; DELETE FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_opengraph-image' AND meta_value = ''; DELETE FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_videositemap-tags' AND meta_value = ''; DELETE FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_videositemap-category' AND meta_value = ''; DELETE FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_videositemap-rating' AND meta_value = ''; DELETE FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_videositemap-thumbnail' AND meta_value = '';
You can obtain his latest list from his GitHub: https://gist.github.com/jdevalk/05d6b0c3e74b4527e3a7
Also, If you want to see what the value are first (how many there are) and make your database work a bit, you can run this:
SELECT * FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_meta-robots' AND meta_value = 'index,follow'
UNION
SELECT * FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_meta-robots-noindex' AND meta_value = '0'
UNION
SELECT * FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_meta-robots-nofollow' AND meta_value = '0'
UNION
SELECT * FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_meta-robots-adv' AND meta_value = 'none'
UNION
SELECT * FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_meta-robots-adv' AND meta_value = ''
UNION
SELECT * FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_sitemap-prio' AND meta_value = '-'
UNION
SELECT * FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_sitemap-include' AND meta_value = '-'
UNION
SELECT * FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_opengraph-description' AND meta_value = ''
UNION
SELECT * FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_google-plus-description' AND meta_value = ''
UNION
SELECT * FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_title' AND meta_value = ''
UNION
SELECT * FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_metadesc' AND meta_value = ''
UNION
SELECT * FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_metakeywords' AND meta_value = ''
UNION
SELECT * FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_focuskw' AND meta_value = ''
UNION
SELECT * FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_bctitle' AND meta_value = ''
UNION
SELECT * FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_canonical' AND meta_value = ''
UNION
SELECT * FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_redirect' AND meta_value = ''
UNION
SELECT * FROM wp_postmeta WHERE meta_key = '_wpseo_video_meta' AND meta_value = ''
UNION
SELECT * FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_video_meta' AND meta_value = 'none'
UNION
SELECT * FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_linkdex' AND meta_value = '0'
UNION
SELECT * FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_newssitemap-original' AND meta_value = ''
UNION
SELECT * FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_newssitemap-stocktickers' AND meta_value = ''
UNION
SELECT * FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_sitemap-html-include' AND meta_value = '-'
UNION
SELECT * FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_opengraph-image' AND meta_value = ''
UNION
SELECT * FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_videositemap-tags' AND meta_value = ''
UNION
SELECT * FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_videositemap-category' AND meta_value = ''
UNION
SELECT * FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_videositemap-rating' AND meta_value = ''
UNION
SELECT * FROM wp_postmeta WHERE meta_key = '_yoast_wpseo_videositemap-thumbnail' AND meta_value = ''
Wouldn’t…
DELETE FROM wp_postmeta WHERE meta_key like ‘_yoast%
…do the trick?
You’re 96.3% right! There is only one table that wouldn’t cover (_wpseo_video_meta). However, now that I carefully look at the code we are looking at different meta values for the different tables (many repeat but not all, and there are differences). So, unless you want to wipe ALL data not just the empty data, you would need to use what’s in the post.