How to Clean Up and Optimize the WordPress Database?

Post revisions, spam comments, expired transients, and orphaned metadata quietly accumulate in every WordPress database over time, and none of it gets cleaned up automatically. A site that's been running for a few years without any database maintenance can easily have a wp_posts table where revisions outnumber actual published content ten to one, and a wp_options table bloated with autoloaded transients that get pulled into memory on every single page load whether they're still relevant or not.

None of this is dangerous by itself, WordPress handles a large database just fine, but it does make every query marginally slower, every backup larger and slower to restore, and every database-related operation (search, admin list screens, migrations) more sluggish than it needs to be. This guide covers exactly what's safe to remove, real SQL to do it directly, the equivalent WP-CLI commands, and how to actually reclaim the freed disk space afterward with OPTIMIZE TABLE.

Before You Touch Anything: Back Up Your Database

This is not optional. Every command in this guide modifies or deletes data directly, and while everything here targets data that's genuinely safe to remove, a mistyped WHERE clause in raw SQL can delete far more than intended. Take a full database export first:

wp db export backup-before-cleanup-$(date +%F).sql

If you don't have WP-CLI available, see the full walkthrough in how to create a WordPress backup manually for the mysqldump and phpMyAdmin equivalents.

What Actually Bloats a WordPress Database

Four categories account for the overwhelming majority of unnecessary database growth:

• Post revisions — WordPress saves a new revision every time you update a post or page, by default with no limit, so a frequently-edited page can accumulate dozens or hundreds of revisions over its lifetime.
• Spam and trashed comments — marked as spam or moved to trash, but not actually deleted from the database until you empty them manually (or a plugin does it on a schedule).
• Expired transients — WordPress's short-lived cache API, stored in wp_options by default (unless you're running persistent object caching, in which case transients don't touch the database at all). Expired transients are supposed to be cleaned up automatically but frequently aren't, especially ones set by plugins that were later deactivated.
• Orphaned post meta — metadata rows left behind in wp_postmeta after their parent post has been deleted, since deleting a post doesn't always cascade to delete its associated meta rows cleanly.

Method 1: Clean Up With WP-CLI (Recommended If Available)

WP-CLI wraps most of this into single, safe commands that understand WordPress's own data model, rather than raw SQL you have to get exactly right yourself.

Delete Post Revisions

# Count how many revisions exist first
wp post list --post_type=revision --format=count

# Delete them all
wp post list --post_type=revision --format=ids | xargs -r wp post delete --force

Empty Spam and Trashed Comments

wp comment delete $(wp comment list --status=spam --format=ids) --force
wp comment delete $(wp comment list --status=trash --format=ids) --force

Delete Expired Transients

wp transient delete --expired

Optimize Every Table

wp db optimize

wp db optimize runs OPTIMIZE TABLE against every table in your WordPress database, which is the step that actually reclaims disk space after the rows above are deleted, since DELETE statements alone leave the freed space allocated to the table without returning it to the filesystem.

Method 2: Clean Up With Raw SQL

If WP-CLI isn't available (common on some shared hosts), the same operations can be run directly through phpMyAdmin's SQL tab, or via mysql over SSH. Replace wp_ with your actual table prefix if you customized it (check $table_prefix in wp-config.php).

Delete Post Revisions sql

DELETE FROM wp_posts WHERE post_type = 'revision';

Delete Orphaned Post Meta

DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON pm.post_id = wp.ID
WHERE wp.ID IS NULL;

This targets only meta rows whose parent post no longer exists at all (the LEFT JOIN ... WHERE wp.ID IS NULL pattern finds rows with no match), so it never touches meta belonging to a post that's still present, even if that post is just a draft.

Empty Spam and Trashed Comments meta

DELETE FROM wp_comments WHERE comment_approved = 'spam';
DELETE FROM wp_comments WHERE comment_approved = 'trash';

Deleting comments this way skips wp_commentmeta cleanup, though, so follow it with the same orphan-cleanup pattern used above for post meta:

DELETE cm FROM wp_commentmeta cm
LEFT JOIN wp_comments c ON cm.comment_id = c.comment_ID
WHERE c.comment_ID IS NULL;

Delete Expired Transients sql

Transients are stored as pairs of rows in wp_options: one holding the value, one holding its expiration timestamp. This deletes both halves of any transient whose expiration timestamp has already passed:

DELETE FROM wp_options
WHERE option_name LIKE '\_transient\_timeout\_%'
  AND option_value < UNIX_TIMESTAMP();

DELETE FROM wp_options
WHERE option_name LIKE '\_transient\_%'
  AND option_name NOT LIKE '\_transient\_timeout\_%'
  AND CONCAT('_transient_timeout_', SUBSTRING(option_name, 12)) NOT IN (
      SELECT option_name FROM wp_options WHERE option_name LIKE '\_transient\_timeout\_%'
  );

The backslashes before the underscores (\_transient\_) escape MySQL's LIKE wildcard behavior, since an unescaped underscore matches any single character, not a literal underscore, which could otherwise match unintended option names.

Reclaim Disk Space With OPTIMIZE TABLE

Running DELETE statements removes rows but doesn't automatically shrink the table file on disk (particularly with the InnoDB storage engine most WordPress installs use). Run this after your cleanup to actually reclaim the freed space:

OPTIMIZE TABLE wp_posts, wp_postmeta, wp_comments, wp_commentmeta, wp_options;

On a large table, OPTIMIZE TABLE briefly locks it while it rebuilds, so it's worth running during lower-traffic hours on a busy site, rather than at peak load.

Method 3: Use a Database Cleanup Plugin

If you'd rather not run SQL or WP-CLI commands directly, a plugin like WP-Optimize or Advanced Database Cleaner wraps the same operations into a dashboard interface:

1. Install and activate WP-Optimize.
2. Go to WP-Optimize → Database, where each cleanup category (revisions, spam comments, transients, orphaned meta) is listed with a checkbox and a count of how many rows it would affect.
3. Review the counts, uncheck anything you're unsure about, and click Run all selected optimizations.
4. Under the Table Optimization tab, run OPTIMIZE TABLE against the affected tables, exactly equivalent to the SQL command above.

This is the safest option for anyone not fully comfortable running raw SQL against a production database, since the plugin shows you exactly what it's about to delete before it deletes it.

Limiting Future Database Bloat

Cleaning up once is only half the job; without addressing the cause, revisions and transients simply start accumulating again immediately. Two changes prevent most future bloat:

• Limit or disable post revisions going forward, covered in full in a dedicated guide, using the WP_POST_REVISIONS constant in wp-config.php.
• Move transients off the database entirely by setting up persistent object caching with Redis, which automatically routes set_transient() / get_transient() calls to Redis instead of wp_options, so they never touch the database or contribute to its size at all.

Scheduling a recurring cleanup (a plugin like WP-Optimize supports automatic weekly cleanups; a WP-CLI version can go in a cron job) keeps the database from creeping back to where it started, rather than needing a manual cleanup pass every few months.

Checking Table Sizes Before and After Cleanup

It's worth measuring the actual impact of a cleanup, both to confirm it worked and to identify which tables are worth focusing on next time. This query lists every table in your WordPress database sorted by size, largest first:

SELECT
    table_name AS "Table",
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)",
    table_rows AS "Approx. Rows"
FROM information_schema.TABLES
WHERE table_schema = 'your_database_name'
ORDER BY (data_length + index_length) DESC
LIMIT 10;

Replace your_database_name with the value of DB_NAME from your wp-config.php. Run this before and after your cleanup pass to see the concrete difference; on a site that's never had its revisions cleared, it's common to see wp_posts shrink noticeably once revisions are removed and the table is optimized.

To specifically check how much of your wp_options table is being autoloaded (pulled into memory on every single page load, regardless of whether that request needs it), run:

SELECT
    SUM(LENGTH(option_value)) / 1024 / 1024 AS "Autoloaded Size (MB)",
    COUNT(*) AS "Autoloaded Rows"
FROM wp_options
WHERE autoload = 'yes';

An autoloaded size under a few hundred KB is typical for a healthy site. If this number climbs into multiple megabytes, it's usually a sign that a plugin (often one that's since been deactivated but never fully uninstalled) is storing large amounts of data with autoload set to yes when it doesn't need to be loaded on every request at all. Finding the worst offenders:

SELECT option_name, LENGTH(option_value) AS size
FROM wp_options
WHERE autoload = 'yes'
ORDER BY size DESC
LIMIT 20;

Investigate any unfamiliar option name at the top of that list before deleting it directly, since it may still be actively used; the safer fix, if the plugin itself supports it, is usually to change that specific option's autoload flag rather than deleting the row outright:

UPDATE wp_options SET autoload = 'no' WHERE option_name = 'some_plugin_large_cache_option';

Identifying What's Actually Taking Up Space

Beyond the four standard categories covered above, larger or older sites sometimes accumulate less obvious bloat worth checking for specifically:

• Duplicate or leftover post meta from a removed plugin. Deactivating and deleting a plugin doesn't always clean up the custom meta keys it added to every post over the years. Search wp_postmeta for a meta_key matching an old plugin's naming convention, and confirm it's genuinely unused before removing it.
• Logged import or migration data. Some import plugins (WooCommerce product importers, in particular) leave behind large temporary rows in wp_options or custom tables used only during the import process itself, which are safe to remove once the import is confirmed successful.
• An oversized wp_actionscheduler_* set of tables, if you run WooCommerce or any plugin using the Action Scheduler library for background jobs. These tables can grow substantially if scheduled actions complete but aren't cleaned up; most plugins using Action Scheduler include their own cleanup routine, but it's worth confirming it's actually running via Tools → Scheduled Actions if that screen is available.

Frequently Asked Questions (FAQ) About WordPress Database Cleanup

Conclusion

A WordPress database doesn't clean up after itself: revisions, spam comments, and expired transients accumulate indefinitely unless something actively removes them, and none of it announces itself as a problem until queries start slowing down or a backup takes noticeably longer than it used to. The cleanup itself, whether through WP-CLI, direct SQL, or a plugin like WP-Optimize, targets exactly the same four categories every time: revisions, spam, expired transients, and orphaned meta, followed by OPTIMIZE TABLE to actually reclaim the disk space the deletions freed up.

The more durable fix is addressing the cause rather than repeating the cleanup indefinitely: limit post revisions going forward, and move transients off the database entirely with a persistent object cache. Combined, these two changes mean the next cleanup you run months from now finds a database that barely needs it.

Here are a few additional resources if you want to go deeper:

• WP-CLI Command Reference: wp db — the full list of database subcommands, including optimize and query.
• WordPress Developer Reference: Transients API — how transients are stored and expired under the hood.
• MySQL Documentation: OPTIMIZE TABLE — full details on what OPTIMIZE TABLE does per storage engine.