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 sites, this often goes unnoticed, especially with caching. On larger or long-running sites, however, it can slow queries, increase storage use, and raise hosting costs.

For years, I relied on cleanup plugins to “fix” these problems for me. They promised one-click optimization and automatic maintenance, so I assumed they were doing the right thing. In reality, I had very little visibility into what they were actually changing. Some added their own tables and settings. Others left behind more data than they removed. In a few cases, I later discovered that important records had been modified or deleted without any clear explanation.

Over time, it became clear that many of these tools were solving one problem while quietly creating another: more database clutter and less transparency.

That experience is what led me to move away from automated cleanup plugins and toward a manual, SQL-based approach. This guide walks you through that process. It focuses on understanding your data, reviewing every change, and maintaining full control over what stays and what goes.

Why Manual SQL Cleanup Is Safer and More Precise

Using SQL directly gives you full visibility into what is being removed and why. You control every operation and can verify results before making changes. Unlike generic cleanup plugins, manual queries can be tailored to your site’s actual data structure.

Many 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:

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

Always run the SELECT statement first and review the results carefully. This approach trades speed for reliability, which is essential when working on production databases.

Before You Start

• Create a full database backup.
• Adjust table prefixes in the SQL statements if your site doesn’t 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 Post Drafts and Auto Drafts

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

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 Common Unused Metadata

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

Remove Orphaned Entries

This query finds posts that reference a missing parent post or term. These records usually result from incomplete deletions. It works by checking whether the post_parent field points to a valid post or term. If neither exists, the record is orphaned.

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;

Cleaning Post Meta (wp_postmeta)

Remove Common Unused Metadata

These meta keys are frequently left behind by WordPress or plugins.

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

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 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)

Remove Orphaned Terms

This query is designed to find orphaned or unused WordPress terms and their taxonomies. In simple terms, it looks for terms that:

• Are not used by any posts.
• Do not have child taxonomies.
• Are not referenced elsewhere.

This query ensures the term is not used by any post and is not acting as a parent for other terms. 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;

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 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

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 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 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)

These options 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 gives you full control over what data is removed, ensuring accuracy and preventing accidental loss. Plugins may be convenient, but they trade transparency for speed. A careful, SQL-driven approach results in a cleaner, faster, and more maintainable WordPress database.