Manual WordPress Database Cleanup Using SQL Without Plugins

WordPress sites inevitably accumulate database bloat over time. This buildup can include orphaned records, unused metadata, administrative leftovers, and spam or trashed content. On small websites, this often goes unnoticed, especially with caching. On larger or long-running websites, however, excessive data can increase storage requirements and, depending on the tables and queries involved, contribute to slower database operations.

It is common to rely on cleanup plugins to “fix” these problems. Plugins promise one-click optimization and automatic maintenance, which can create the impression that they are handling things correctly. These plugins typically offer very little visibility into what changes are actually being made. Some even leave behind more data than they remove by adding new tables and settings to the database. In other cases, plugins may modify or delete important records without any clear explanation or warning.

While these tools may solve one problem, they may quietly create another: more database clutter and less transparency.

These limitations make a manual, SQL-based approach preferable when precise control over database changes is required. This article describes that process, focusing on understanding the data, reviewing every change, and maintaining full control over what stays and what goes.

Why Use Manual SQL for Database Cleanup

Using SQL directly provides full visibility into what is being removed and why. Every operation can be reviewed and verified before changes are made. Unlike generic cleanup plugins, manual queries can be tailored to the actual data structure.

Some automated cleanup tools rely on simplified assumptions about WordPress relationships. They often ignore links between posts, terms, taxonomies, and metadata. This can lead to broken navigation, missing categories, and corrupted archives. Manual SQL cleanup avoids these problems by making every deletion explicit and reviewable.

How This Guide Works

Each cleanup task includes two queries:

• SELECT query to preview affected records.
• DELETE or UPDATE query to apply changes.

Never run a destructive query blindly. Always run the SELECT statement first and review the results carefully to confirm which records will be affected before modifying anything. This approach trades speed for reliability, which is essential when working on production databases.

Before Starting

• Create a full database backup.
• Understand how to restore the backup before running destructive queries.
• Adjust table prefixes in the SQL statements if the website does not use the default wp_.
• These queries assume a standard WordPress schema. Plugins or custom code may introduce edge cases.
• Some queries may need to be run multiple times. Removing data from one table can reveal new orphaned records in related tables.

Cleaning Posts (wp_posts)

Remove Post Revisions

Revisions can accumulate quickly and consume unnecessary space.

SELECT * FROM `wp_posts` WHERE post_type = 'revision';
DELETE FROM `wp_posts` WHERE post_type = 'revision';

Remove Unneeded Drafts and Auto-Drafts

Drafts and auto-drafts that are no longer needed can be safely removed.

NOTE: This deletes every post currently in draft status, including drafts that are still being written. Review the SELECT results and confirm none of them are active work before running the DELETE.

SELECT * FROM `wp_posts` WHERE post_status = 'draft';
DELETE FROM `wp_posts` WHERE post_status = 'draft';

SELECT * FROM `wp_posts` WHERE post_status = 'auto-draft';
DELETE FROM `wp_posts` WHERE post_status = 'auto-draft';

Remove Trashed Posts

SELECT * FROM `wp_posts` WHERE post_status = 'trash';
DELETE FROM `wp_posts` WHERE post_status = 'trash';

Remove oEmbed Cache Entries

SELECT * FROM `wp_posts` WHERE post_type = 'oembed_cache';
DELETE FROM `wp_posts` WHERE post_type = 'oembed_cache';

Fix Orphaned Post Parent References

This query finds posts where the post_parent field references a post that no longer exists. These records usually result from incomplete deletions. Rather than deleting the child post, which would destroy valid content, the query resets post_parent to 0, WordPress’s default for “no parent”. This restores the post to a valid state without losing its content.

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
WHERE
  t_posts_child.post_parent <> 0
  AND t_posts_parent.ID IS NULL;

UPDATE
  `wp_posts` t_posts_child
LEFT JOIN
  `wp_posts` t_posts_parent
  ON t_posts_parent.ID = t_posts_child.post_parent
SET
  t_posts_child.post_parent = 0
WHERE
  t_posts_child.post_parent <> 0
  AND t_posts_parent.ID IS NULL;

Cleaning Post Meta (wp_postmeta)

Remove Common Unused Metadata

These meta keys accumulate as stale leftovers from WordPress or plugins.

_oembed_% entries contain cached oEmbed data rather than the original post content, so removing them does not delete the embedded content itself. WordPress can regenerate the cache when needed.

_edit_last and _edit_lock are regenerated automatically the next time a post is edited, so removing them is safe but not strictly cleanup of unused data.

NOTE: Deleting _wp_old_slug records removes the redirect history WordPress uses to automatically handle URL changes when a post slug is renamed. If any posts have been renamed and the old URLs are still in use (in bookmarks, external links, or sitemaps), deleting these records will break those redirects. Review the results of the SELECT query carefully before running the DELETE.

SELECT * FROM `wp_postmeta` WHERE meta_key LIKE '_oembed_%';
DELETE FROM `wp_postmeta` WHERE meta_key LIKE '_oembed_%';

SELECT * FROM `wp_postmeta` WHERE meta_key = '_wp_old_slug';
DELETE FROM `wp_postmeta` WHERE meta_key = '_wp_old_slug';

SELECT * FROM `wp_postmeta` WHERE meta_key = '_wp_old_date';
DELETE FROM `wp_postmeta` WHERE meta_key = '_wp_old_date';

SELECT * FROM `wp_postmeta` WHERE meta_key = '_edit_last';
DELETE FROM `wp_postmeta` WHERE meta_key = '_edit_last';

SELECT * FROM `wp_postmeta` WHERE meta_key = '_edit_lock';
DELETE FROM `wp_postmeta` WHERE meta_key = '_edit_lock';

Remove Orphaned Post Meta Entries

This query removes metadata that references deleted posts.

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;

Cleaning Terms (wp_terms), Term Taxonomies (wp_term_taxonomy), and Term Relationships (wp_term_relationships)

Taxonomy data is relational. Do not delete terms, taxonomies, or relationships based solely on whether an individual table contains apparently unused rows. Review the relationships between all three tables before deleting records.

Remove Terms Not Used by Posts

This query identifies terms whose taxonomy records are not associated with any posts and that are not referenced as a parent by another taxonomy record. Only fully disconnected terms are returned.

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;

Remove Terms Without a Taxonomy Record

This query is designed to find orphaned WordPress terms that have no taxonomy record. It identifies terms that exist in wp_terms but are not registered as a category, tag, or custom taxonomy.

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;

Remove Orphaned Term Relationships

This removes relationship records that no longer point to valid posts or taxonomies.

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;

Remove Orphaned Term Taxonomies

This finds taxonomy entries that are not connected to any term or post.

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;

Recalculate Term Counts

WordPress stores cached post counts for each term. These values often become inaccurate after imports or bulk edits. This query recalculates how many published posts are linked to each term and compares that value to WordPress’s stored count. For discrepancies, the cached count is updated with the correct calculated value.

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;

Cleaning Term Meta (wp_termmeta)

Remove Orphaned Term Meta Entries

This removes metadata that references deleted terms.

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;

Cleaning Comments (wp_comments)

Remove Unapproved Comments

NOTE: This deletes every currently unapproved comment, including legitimate comments that are awaiting moderation.

SELECT * FROM `wp_comments` WHERE comment_approved = '0';
DELETE FROM `wp_comments` WHERE comment_approved = '0';

Remove Spam Comments

SELECT * FROM `wp_comments` WHERE comment_approved = 'spam';
DELETE FROM `wp_comments` WHERE comment_approved = 'spam';

Remove Trashed Comments

SELECT * FROM `wp_comments` WHERE comment_approved = 'trash';
DELETE FROM `wp_comments` WHERE comment_approved = 'trash';

Remove Pingbacks

SELECT * FROM `wp_comments` WHERE comment_type = 'pingback';
DELETE FROM `wp_comments` WHERE comment_type = 'pingback';

Remove Trackbacks

SELECT * FROM `wp_comments` WHERE comment_type = 'trackback';
DELETE FROM `wp_comments` WHERE comment_type = 'trackback';

Cleaning Comment Meta (wp_commentmeta)

Remove Orphaned Comment Meta 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;

Cleaning User Meta (wp_usermeta)

Remove Orphaned User Meta 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;

Cleaning Options (wp_options)

The options covered in this section are temporary and are regenerated automatically.

Remove 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_%';

Remove Session Data

SELECT
  *
FROM
  `wp_options`
WHERE
  option_name LIKE '_wp_session_%';

DELETE
FROM
  `wp_options`
WHERE
  option_name LIKE '_wp_session_%';

Summary

Manual SQL cleanup provides full control over what data is removed, allowing reviews of exactly what will be affected before making changes. Plugins may be convenient, but manual SQL allows greater visibility into exactly what is being changed. A careful, SQL-driven approach can result in a cleaner and, in some cases, more efficient database.