Cleaning a large posmeta database

To clean a large wp_postmeta table in WordPress, you must identify and delete orphaned or unused metadata left behind by deactivated plugins, imported content, or unused themes. The safest approach involves auditing the database to find high-volume keys, verifying they are no longer in use, and then executing targeted deletion queries. 

1. Audit the Database First, determine which meta_key values are consuming the most space. Run this SQL query in phpMyAdmin or your database manager to list the top 20 most common keys:

SELECT meta_key, COUNT(*) as count
FROM wp_postmeta
GROUP BY meta_key
ORDER BY count DESC
LIMIT 20;

You can also check for orphaned rows (metadata linked to deleted posts) using:

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

2. Delete Unused Metadata Once you have identified specific keys that are no longer needed (e.g., from a deleted SEO or social plugin), delete them using a targeted query. Replace 'your_plugin_meta_key' with the actual key name found in your audit:

DELETE FROM wp_postmeta WHERE meta_key = 'your_plugin_meta_key';

For keys with similar prefixes, you can use LIKE:

DELETE FROM wp_postmeta WHERE meta_key LIKE 'example_prefix_%';

3. Best Practices

• Backup First: Always export your database before running DELETE queries. 
• Staging Environment: Test your audit and deletion queries on a staging copy of your site to ensure no functionality breaks. 
• Verify: After deletion, browse your posts to confirm that custom fields and data are still displaying correctly. 
• Plugins: Tools like Advanced Database Cleaner or WP-Sweep can automate parts of this process, but manual SQL queries are often more precise for large, complex tables. 

Cleaning a large posts table

DELETE wp_posts
FROM wp_posts
LEFT JOIN wp_postmeta
ON (Qw812dg_posts.ID = wp_postmeta.post_id
AND Qw812dg_postmeta.meta_key = '_seopress_redirections_type' )
WHERE 1=1
AND ( wp_postmeta.post_id IS NULL )
AND wp_posts.post_type = 'seopress_404'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'future'
OR wp_posts.post_status = 'draft'
OR wp_posts.post_status = 'pending'
OR wp_posts.post_status = 'private')


DELETE FROM `wp_postmeta` 
WHERE `meta_key` IN (
    'seopress_redirections_ua', 
    '_seopress_redirections_ip', 
    '_seopress_404_redirect_date_request', 
    'seopress_redirections_referer',
	'seopress_404_count'
);