WordPress Database Cleanup Without Plugins (SQL Only)

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

  1. As always, please back up the database before executing any changes.
  2. 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.
  3. 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.
  4. 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;

1 thought on “WordPress Database Cleanup Without Plugins (SQL Only)”

Leave a Comment