WordPress sites running for any length of time accumulate unnecessary overhead and bloat. This overhead tends to fall into a few categories, e.g., orphaned data/metadata, administrative overhead, and spam/trash. For small sites, this data accumulation is largely unnoticed as it is unlikely to cause any material performance issues especially if the site employs caching. On larger sites, however, this overhead may become significant in terms of performance degradation as well as increased hosting costs.
If you’re not familiar with working directly in the underlying WordPress database using phpMyAdmin and you only manage the site through the WordPress front-end administration screens, then you may want to consider using plugins to perform database cleanup activities. While most of the following SQL statements are fairly straightforward to understand, there are a few more complicated statements involving Terms, Term Taxonomies, Term Relationships, and Posts. In my opinion, the WordPress schema, with respect to the Term and Post relationship, is convoluted and hard to understand for the typical user.
Why am I writing yet another post about WordPress database cleanup when so many others are already published and so many WordPress database cleanup plugins are available for use? Well, one of the reasons I wrote this post is that many of those other “ultimate” WordPress database cleanup posts are incorrect. As an example, I have found that many of these cleanup scripts do not consider the schema relationship between Terms/Taxonomies and Posts when cleaning orphaned terms and posts. In those instances, menu navigation entries are unintentionally deleted. For those reasons, I prefer to handle the database cleanup myself instead of indiscriminately executing potentially incorrect SQL statements. Simply, I want more control over what is being deleted.
Like those other WordPress cleanup posts, you shouldn’t blindly trust the following SQL statements either. However, unlike those other posts, I have provided two SQL statements for each cleanup activity. The SELECT statement returns the results that will be deleted using the DELETE statement. Please review the SELECT statement results before executing the corresponding DELETE statement in case there are entries that should not be deleted.
Before Starting
- As always, please back up the database before executing any changes.
- If the WordPress site uses a database table prefix other than wp_ in its configuration, then the SQL statements will obviously need to be adjusted.
- These SQL statements are based on a clean WordPress installation and may not consider the impact from unusual configurations or installed plugins. Always execute the provided SELECT statements first to verify that valid data is not unintentionally deleted.
- Some statements may need to be executed repeatedly. Due to the relationship between Terms and Posts, additional orphaned data may be created as entries are removed from either table. If a cleanup has never (or infrequently) been performed, then don’t be surprised if repeated cleanings are needed initially.
Table wp_posts Cleanup
Delete Post Revisions
SELECT * FROM `wp_posts` WHERE post_type = 'revision';
DELETE FROM `wp_posts` WHERE post_type = 'revision';
Delete Draft Posts
SELECT * FROM `wp_posts` WHERE post_status = 'draft';
DELETE FROM `wp_posts` WHERE post_status = 'draft';
Delete Auto Drafts
SELECT * FROM `wp_posts` WHERE post_status = 'auto-draft';
DELETE FROM `wp_posts` WHERE post_status = 'auto-draft';
Delete Trashed Posts
SELECT * FROM `wp_posts` WHERE post_status = 'trash';
DELETE FROM `wp_posts` WHERE post_status = 'trash';
Delete oEmbed Cache
SELECT * FROM `wp_posts` WHERE post_type = 'oembed_cache';
DELETE FROM `wp_posts` WHERE post_type = 'oembed_cache';
Delete Orphaned Entries
SELECT t_posts_child.* FROM `wp_posts` t_posts_child LEFT JOIN `wp_posts` t_posts_parent ON t_posts_parent.ID = t_posts_child.post_parent LEFT JOIN `wp_terms` ON `wp_terms`.term_id = t_posts_child.post_parent WHERE t_posts_child.post_parent <> 0 AND t_posts_parent.ID IS NULL AND `wp_terms`.term_id IS NULL;
DELETE t_posts_child FROM `wp_posts` t_posts_child LEFT JOIN `wp_posts` t_posts_parent ON t_posts_parent.ID = t_posts_child.post_parent LEFT JOIN `wp_terms` ON `wp_terms`.term_id = t_posts_child.post_parent WHERE t_posts_child.post_parent <> 0 AND t_posts_parent.ID IS NULL AND `wp_terms`.term_id IS NULL;
Table wp_postmeta Cleanup
Delete oEmbed Cache
SELECT * FROM `wp_postmeta` WHERE meta_key = 'oembed_cache';
DELETE FROM `wp_postmeta` WHERE meta_key = 'oembed_cache';
Delete Old Slugs
SELECT * FROM `wp_postmeta` WHERE meta_key = '_wp_old_slug';
DELETE FROM `wp_postmeta` WHERE meta_key = '_wp_old_slug';
Delete Old Dates
SELECT * FROM `wp_postmeta` WHERE meta_key = '_wp_old_date';
DELETE FROM `wp_postmeta` WHERE meta_key = '_wp_old_date';
Delete Last Editors
SELECT * FROM `wp_postmeta` WHERE meta_key = '_edit_last';
DELETE FROM `wp_postmeta` WHERE meta_key = '_edit_last';
Delete Edit Locks
SELECT * FROM `wp_postmeta` WHERE meta_key = '_edit_lock';
DELETE FROM `wp_postmeta` WHERE meta_key = '_edit_lock';
Delete Orphaned Entries
SELECT `wp_postmeta`.* FROM `wp_postmeta` LEFT JOIN `wp_posts` ON `wp_posts`.ID = `wp_postmeta`.post_id WHERE `wp_posts`.ID IS NULL;
DELETE `wp_postmeta` FROM `wp_postmeta` LEFT JOIN `wp_posts` ON `wp_posts`.ID = `wp_postmeta`.post_id WHERE `wp_posts`.ID IS NULL;
Table wp_terms and wp_term_taxonomy Cleanup
Delete Orphaned Entries
SELECT `wp_terms`.*, t_term_taxonomy_sibling.* FROM `wp_terms` LEFT JOIN `wp_term_taxonomy` t_term_taxonomy_sibling ON t_term_taxonomy_sibling.term_id = `wp_terms`.term_id LEFT JOIN `wp_term_taxonomy` t_term_taxonomy_parent ON t_term_taxonomy_parent.parent = `wp_terms`.term_id LEFT JOIN `wp_term_relationships` ON `wp_term_relationships`.term_taxonomy_id = t_term_taxonomy_sibling.term_taxonomy_id WHERE t_term_taxonomy_parent.parent IS NULL AND `wp_term_relationships`.term_taxonomy_id IS NULL;
DELETE `wp_terms`, t_term_taxonomy_sibling FROM `wp_terms` LEFT JOIN `wp_term_taxonomy` t_term_taxonomy_sibling ON t_term_taxonomy_sibling.term_id = `wp_terms`.term_id LEFT JOIN `wp_term_taxonomy` t_term_taxonomy_parent ON t_term_taxonomy_parent.parent = `wp_terms`.term_id LEFT JOIN `wp_term_relationships` ON `wp_term_relationships`.term_taxonomy_id = t_term_taxonomy_sibling.term_taxonomy_id WHERE t_term_taxonomy_parent.parent IS NULL AND `wp_term_relationships`.term_taxonomy_id IS NULL;
Table wp_relationships Cleanup
Delete Orphaned Entries
SELECT `wp_term_relationships`.* FROM `wp_term_relationships` LEFT JOIN `wp_posts` ON `wp_posts`.ID = `wp_term_relationships`.object_id LEFT JOIN `wp_term_taxonomy` ON `wp_term_taxonomy`.term_taxonomy_id = `wp_term_relationships`.term_taxonomy_id WHERE `wp_posts`.ID IS NULL AND `wp_term_taxonomy`.term_taxonomy_id IS NULL;
DELETE `wp_term_relationships` FROM `wp_term_relationships` LEFT JOIN `wp_posts` ON `wp_posts`.ID = `wp_term_relationships`.object_id LEFT JOIN `wp_term_taxonomy` ON `wp_term_taxonomy`.term_taxonomy_id = `wp_term_relationships`.term_taxonomy_id WHERE `wp_posts`.ID IS NULL AND `wp_term_taxonomy`.term_taxonomy_id IS NULL;
Table wp_term_taxonomy Cleanup
Delete Orphaned Entries
SELECT `wp_term_taxonomy`.* FROM `wp_term_taxonomy` LEFT JOIN `wp_term_relationships` ON `wp_term_relationships`.term_taxonomy_id = `wp_term_taxonomy`.term_taxonomy_id LEFT JOIN `wp_terms` ON `wp_terms`.term_id = `wp_term_taxonomy`.term_id WHERE `wp_term_relationships`.term_taxonomy_id IS NULL AND `wp_terms`.term_id IS NULL;
DELETE `wp_term_taxonomy` FROM `wp_term_taxonomy` LEFT JOIN `wp_term_relationships` ON `wp_term_relationships`.term_taxonomy_id = `wp_term_taxonomy`.term_taxonomy_id LEFT JOIN `wp_terms` ON `wp_terms`.term_id = `wp_term_taxonomy`.term_id WHERE `wp_term_relationships`.term_taxonomy_id IS NULL AND `wp_terms`.term_id IS NULL;
Table wp_terms Cleanup
Delete Orphaned Entries
SELECT `wp_terms`.* FROM `wp_terms` LEFT JOIN `wp_term_taxonomy` ON `wp_term_taxonomy`.term_id = `wp_terms`.term_id WHERE `wp_term_taxonomy`.term_id IS NULL;
DELETE `wp_terms` FROM `wp_terms` LEFT JOIN `wp_term_taxonomy` ON `wp_term_taxonomy`.term_id = `wp_terms`.term_id WHERE `wp_term_taxonomy`.term_id IS NULL;
Table wp_termmeta Cleanup
Delete Orphaned Entries
SELECT `wp_termmeta`.* FROM `wp_termmeta` LEFT JOIN `wp_terms` ON `wp_terms`.term_id = `wp_termmeta`.term_id WHERE `wp_terms`.term_id IS NULL;
DELETE `wp_termmeta` FROM `wp_termmeta` LEFT JOIN `wp_terms` ON `wp_terms`.term_id = `wp_termmeta`.term_id WHERE `wp_terms`.term_id IS NULL;
Table wp_comments Cleanup
Delete Unapproved Comments
SELECT * FROM `wp_comments` WHERE comment_approved = '0';
DELETE FROM `wp_comments` WHERE comment_approved = '0';
Delete Spam Comments
SELECT * FROM `wp_comments` WHERE comment_approved = 'spam';
DELETE FROM `wp_comments` WHERE comment_approved = 'spam';
Delete Trashed Comments
SELECT * FROM `wp_comments` WHERE comment_approved = 'trash';
DELETE FROM `wp_comments` WHERE comment_approved = 'trash';
Delete Pingbacks
SELECT * FROM `wp_comments` WHERE comment_type = 'pingback';
DELETE FROM `wp_comments` WHERE comment_type = 'pingback';
Delete Trackbacks
SELECT * FROM `wp_comments` WHERE comment_type = 'trackback';
DELETE FROM `wp_comments` WHERE comment_type = 'trackback';
Table wp_commentmeta Cleanup
Delete Orphaned Entries
SELECT `wp_commentmeta`.* FROM `wp_commentmeta` LEFT JOIN `wp_comments` ON `wp_comments`.comment_ID = `wp_commentmeta`.comment_id WHERE `wp_comments`.comment_ID IS NULL;
DELETE `wp_commentmeta` FROM `wp_commentmeta` LEFT JOIN `wp_comments` ON `wp_comments`.comment_ID = `wp_commentmeta`.comment_id WHERE `wp_comments`.comment_ID IS NULL;
Table wp_usermeta Cleanup
Delete Orphaned Entries
SELECT `wp_usermeta`.* FROM `wp_usermeta` LEFT JOIN `wp_users` ON `wp_users`.ID = `wp_usermeta`.user_id WHERE `wp_users`.ID IS NULL;
DELETE `wp_usermeta` FROM `wp_usermeta` LEFT JOIN `wp_users` ON `wp_users`.ID = `wp_usermeta`.user_id WHERE `wp_users`.ID IS NULL;
Table wp_options Cleanup
Delete Transients
SELECT * FROM `wp_options` WHERE option_name LIKE '_transient_%' OR option_name LIKE '_site_transient_%';
DELETE FROM `wp_options` WHERE option_name LIKE '_transient_%' OR option_name LIKE '_site_transient_%';
Delete Session Tokens
SELECT * FROM `wp_options` WHERE option_name LIKE '_wp_session_%';
DELETE FROM `wp_options` WHERE option_name LIKE '_wp_session_%';
Table wp_term_taxonomy Updates
Update Category and Tag Counts
SELECT `wp_term_taxonomy`.term_taxonomy_id, `wp_term_taxonomy`.taxonomy, `wp_terms`.term_id, `wp_terms`.name, `wp_term_taxonomy`.count, COUNT( `wp_term_relationships`.object_id ) AS t_term_relationship_count FROM `wp_term_taxonomy` LEFT JOIN `wp_term_relationships` ON `wp_term_relationships`.term_taxonomy_id = `wp_term_taxonomy`.term_taxonomy_id LEFT JOIN `wp_terms` ON `wp_terms`.term_id = `wp_term_taxonomy`.`term_id` LEFT JOIN `wp_posts` ON `wp_posts`.ID = `wp_term_relationships`.object_id WHERE `wp_posts`.post_status = 'publish' GROUP BY `wp_term_taxonomy`.term_taxonomy_id, `wp_term_taxonomy`.taxonomy, `wp_terms`.term_id, `wp_terms`.name, `wp_term_taxonomy`.count HAVING t_term_relationship_count <> `wp_term_taxonomy`.count;
UPDATE `wp_term_taxonomy` t_term_taxonomy_outer INNER JOIN( SELECT `wp_term_taxonomy`.term_taxonomy_id, `wp_term_taxonomy`.count, COUNT( `wp_term_relationships`.object_id ) AS t_term_relationship_count FROM `wp_term_taxonomy` LEFT JOIN `wp_term_relationships` ON `wp_term_relationships`.term_taxonomy_id = `wp_term_taxonomy`.term_taxonomy_id LEFT JOIN `wp_posts` ON `wp_posts`.ID = `wp_term_relationships`.object_id WHERE `wp_posts`.post_status = 'publish' GROUP BY `wp_term_taxonomy`.term_taxonomy_id, `wp_term_taxonomy`.count HAVING t_term_relationship_count <> `wp_term_taxonomy`.count ) t_term_taxonomy_inner ON t_term_taxonomy_inner.term_taxonomy_id = t_term_taxonomy_outer.term_taxonomy_id SET t_term_taxonomy_outer.count = t_term_taxonomy_inner.t_term_relationship_count;
Full Script
DELETE FROM `wp_posts` WHERE post_type = 'revision'; DELETE FROM `wp_posts` WHERE post_status = 'draft'; DELETE FROM `wp_posts` WHERE post_status = 'auto-draft'; DELETE FROM `wp_posts` WHERE post_status = 'trash'; DELETE FROM `wp_posts` WHERE post_type = 'oembed_cache'; DELETE t_posts_child FROM `wp_posts` t_posts_child LEFT JOIN `wp_posts` t_posts_parent ON t_posts_parent.ID = t_posts_child.post_parent LEFT JOIN `wp_terms` ON `wp_terms`.term_id = t_posts_child.post_parent WHERE t_posts_child.post_parent <> 0 AND t_posts_parent.ID IS NULL AND `wp_terms`.term_id IS NULL; DELETE FROM `wp_postmeta` WHERE meta_key = 'oembed_cache'; DELETE FROM `wp_postmeta` WHERE meta_key = '_wp_old_slug'; DELETE FROM `wp_postmeta` WHERE meta_key = '_wp_old_date'; DELETE FROM `wp_postmeta` WHERE meta_key = '_edit_last'; DELETE FROM `wp_postmeta` WHERE meta_key = '_edit_lock'; DELETE `wp_postmeta` FROM `wp_postmeta` LEFT JOIN `wp_posts` ON `wp_posts`.ID = `wp_postmeta`.post_id WHERE `wp_posts`.ID IS NULL; DELETE `wp_terms`, t_term_taxonomy_sibling FROM `wp_terms` LEFT JOIN `wp_term_taxonomy` t_term_taxonomy_sibling ON t_term_taxonomy_sibling.term_id = `wp_terms`.term_id LEFT JOIN `wp_term_taxonomy` t_term_taxonomy_parent ON t_term_taxonomy_parent.parent = `wp_terms`.term_id LEFT JOIN `wp_term_relationships` ON `wp_term_relationships`.term_taxonomy_id = t_term_taxonomy_sibling.term_taxonomy_id WHERE t_term_taxonomy_parent.parent IS NULL AND `wp_term_relationships`.term_taxonomy_id IS NULL; DELETE `wp_term_relationships` FROM `wp_term_relationships` LEFT JOIN `wp_posts` ON `wp_posts`.ID = `wp_term_relationships`.object_id LEFT JOIN `wp_term_taxonomy` ON `wp_term_taxonomy`.term_taxonomy_id = `wp_term_relationships`.term_taxonomy_id WHERE `wp_posts`.ID IS NULL AND `wp_term_taxonomy`.term_taxonomy_id IS NULL; DELETE `wp_term_taxonomy` FROM `wp_term_taxonomy` LEFT JOIN `wp_term_relationships` ON `wp_term_relationships`.term_taxonomy_id = `wp_term_taxonomy`.term_taxonomy_id LEFT JOIN `wp_terms` ON `wp_terms`.term_id = `wp_term_taxonomy`.term_id WHERE `wp_term_relationships`.term_taxonomy_id IS NULL AND `wp_terms`.term_id IS NULL; DELETE `wp_terms` FROM `wp_terms` LEFT JOIN `wp_term_taxonomy` ON `wp_term_taxonomy`.term_id = `wp_terms`.term_id WHERE `wp_term_taxonomy`.term_id IS NULL; DELETE `wp_termmeta` FROM `wp_termmeta` LEFT JOIN `wp_terms` ON `wp_terms`.term_id = `wp_termmeta`.term_id WHERE `wp_terms`.term_id IS NULL; DELETE FROM `wp_comments` WHERE comment_approved = '0'; DELETE FROM `wp_comments` WHERE comment_approved = 'spam'; DELETE FROM `wp_comments` WHERE comment_approved = 'trash'; DELETE FROM `wp_comments` WHERE comment_type = 'pingback'; DELETE FROM `wp_comments` WHERE comment_type = 'trackback'; DELETE `wp_commentmeta` FROM `wp_commentmeta` LEFT JOIN `wp_comments` ON `wp_comments`.comment_ID = `wp_commentmeta`.comment_id WHERE `wp_comments`.comment_ID IS NULL; DELETE `wp_usermeta` FROM `wp_usermeta` LEFT JOIN `wp_users` ON `wp_users`.ID = `wp_usermeta`.user_id WHERE `wp_users`.ID IS NULL; DELETE FROM `wp_options` WHERE option_name LIKE '_transient_%' OR option_name LIKE '_site_transient_%'; DELETE FROM `wp_options` WHERE option_name LIKE '_wp_session_%'; UPDATE `wp_term_taxonomy` t_term_taxonomy_outer INNER JOIN( SELECT `wp_term_taxonomy`.term_taxonomy_id, `wp_term_taxonomy`.count, COUNT( `wp_term_relationships`.object_id ) AS t_term_relationship_count FROM `wp_term_taxonomy` LEFT JOIN `wp_term_relationships` ON `wp_term_relationships`.term_taxonomy_id = `wp_term_taxonomy`.term_taxonomy_id LEFT JOIN `wp_posts` ON `wp_posts`.ID = `wp_term_relationships`.object_id WHERE `wp_posts`.post_status = 'publish' GROUP BY `wp_term_taxonomy`.term_taxonomy_id, `wp_term_taxonomy`.count HAVING t_term_relationship_count <> `wp_term_taxonomy`.count ) t_term_taxonomy_inner ON t_term_taxonomy_inner.term_taxonomy_id = t_term_taxonomy_outer.term_taxonomy_id SET t_term_taxonomy_outer.count = t_term_taxonomy_inner.t_term_relationship_count;