How to Clean Up and Optimize the WordPress Database?

A WordPress database grows quietly. Every save creates a revision. Plugins store temporary data as transients and forget to remove it. Deleted plugins leave their settings behind, and some of those settings are loaded on every single page view. After a few years, a site with 300 posts can have a 900 MB database, a wp_options table that loads several megabytes on each request, and backups that take ten minutes. None of this breaks the site outright. It just makes everything a little slower, including the admin, backups, migrations, and every uncached page.

This article covers how to clean up and optimize the WordPress database safely: taking a backup first, measuring where the space goes, cleaning revisions, auto-drafts, trash, spam, and expired transients, removing orphaned metadata, fixing oversized autoloaded options, optimizing tables, and keeping the database lean with ongoing settings. The examples use WP-CLI and SQL, with notes on plugin alternatives.

Before You Start: Back Up the Database

Every step in this article deletes data. Most of it is junk, but a wrong WHERE clause or a plugin that actually needed a "leftover" option can break things. Take a backup first, and know how to restore it.

# Terminal
cd /var/www/example.com/public
wp db export ~/backups/example-$(date +%F-%H%M).sql

wp db export writes a full SQL dump using the credentials in wp-config.php. Copy it off the server, or at least out of the web root. To restore:

# Terminal
wp db import ~/backups/example-2026-10-09-0930.sql

If you do not have shell access, use your host's backup tool or a backup plugin. The options are covered in how to create a backup for a WordPress website. For large cleanups, work on a staging copy first and repeat the steps on production once you are confident.

A Note on Table Prefixes

The examples below use the default wp_ table prefix. Many sites use a different one, such as wp7k_ or site_. Find yours:

# Terminal
wp db prefix

Replace wp_ in every SQL query with your prefix. In PHP, never hard-code the prefix; use $wpdb->posts, $wpdb->postmeta, $wpdb->options, and $wpdb->prefix.

Step 1: Measure Where the Space Goes

Do not clean blindly. Start by finding the largest tables:

# Terminal
wp db size --tables --human-readable

Or with SQL, which also shows how much space is reclaimable:

-- Largest tables in the WordPress database
SELECT
  table_name,
  ROUND((data_length + index_length) / 1024 / 1024, 1) AS size_mb,
  ROUND(data_free / 1024 / 1024, 1) AS free_mb,
  table_rows
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC
LIMIT 20;

Run SQL through WP-CLI so you do not need separate credentials:

# Terminal
wp db query "SELECT table_name, ROUND((data_length + index_length)/1024/1024,1) AS size_mb FROM information_schema.tables WHERE table_schema = DATABASE() ORDER BY size_mb DESC LIMIT 20;"

Typical findings:

Large table	Usual cause
wp_posts	Revisions, auto-drafts, trashed posts
wp_postmeta	Revision metadata, orphaned rows, page builder data
wp_options	Expired transients, plugin leftovers, large autoloaded options
wp_comments, wp_commentmeta	Spam and trashed comments
Plugin tables (logs, sessions, stats)	Logging and analytics plugins without retention limits
wp_actionscheduler_*	Completed and failed Action Scheduler jobs

Plugin log tables are often the biggest single win. Check the plugin's own settings for a retention period before deleting anything from its tables by hand.

Step 2: Delete Old Revisions

WordPress keeps every revision of every post and page by default. A post edited 80 times has 80 revisions, each a full copy of the content, plus metadata.

Count them first:

# Terminal
wp post list --post_type=revision --format=count

Delete all revisions with WP-CLI. --force deletes permanently instead of trashing, and WordPress removes each revision's metadata along with it:

# Terminal
wp post delete $(wp post list --post_type=revision --format=ids) --force

On sites with tens of thousands of revisions, that command line becomes too long. Delete in batches instead:

# Terminal
while ids=$(wp post list --post_type=revision --format=ids --posts_per_page=500) && [ -n "$ids" ]; do
  wp post delete $ids --force --quiet
done

If you want to keep recent history, delete only old revisions, for example anything older than 90 days. A short script run with wp eval-file does this with core functions:

// delete-old-revisions.php  (run with: wp eval-file delete-old-revisions.php)
<?php
$ids = get_posts(
array(
'post_type'      => 'revision',
'post_status'    => 'inherit',
'posts_per_page' => -1,
'fields'         => 'ids',
'date_query'     => array( array( 'before' => '90 days ago' ) ),
)
);

foreach ( $ids as $id ) {
wp_delete_post_revision( $id );
}

WP_CLI::success( sprintf( 'Deleted %d revisions older than 90 days.', count( $ids ) ) );

Then stop revisions from piling up again by limiting how many WordPress keeps. That is covered in detail in how to limit and manage post revisions in WordPress, but the short version is one line in wp-config.php:

// wp-config.php
define( 'WP_POST_REVISIONS', 10 );

Step 3: Remove Auto-Drafts, Trash, and Spam

WordPress creates an auto-draft every time someone opens the "Add New" screen, even if they leave without typing. WordPress deletes auto-drafts older than seven days during a daily scheduled event, but if cron is not running reliably, they accumulate.

# Terminal
# Auto-drafts
wp post delete $(wp post list --post_status=auto-draft --post_type=any --format=ids) --force

# Trashed posts and pages
wp post delete $(wp post list --post_status=trash --post_type=any --format=ids) --force

# 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

If a command reports that no IDs were passed, there is nothing to delete for that type.

Trashed items are removed automatically after 30 days by default. To shorten that, set EMPTY_TRASH_DAYS in wp-config.php:

// wp-config.php
define( 'EMPTY_TRASH_DAYS', 7 );

Step 4: Clear Expired Transients

Transients are cached values with an expiry time, stored in wp_options when no persistent object cache is installed. Expired transients are supposed to be cleaned up, but many linger. Remove them:

# Terminal
wp transient delete --expired

To remove every transient, including unexpired ones:

# Terminal
wp transient delete --all

Deleting all transients is safe in principle, because code must handle a missing transient by regenerating it, but expect a few slower requests while caches rebuild.

If the site uses a persistent object cache such as Redis or Memcached, transients live there instead of in wp_options, and this step matters less. How that works is explained in mastering object cache in WordPress.

Step 5: Remove Orphaned Metadata

Metadata rows point to a parent post, comment, user, or term. When the parent is deleted by direct SQL or a buggy plugin, the metadata stays behind. Count orphaned post metadata first:

-- Count postmeta rows whose post no longer exists
SELECT COUNT(*)
FROM wp_postmeta pm
LEFT JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL;

If the number is significant, delete them:

-- Delete postmeta rows whose post no longer exists
DELETE pm
FROM wp_postmeta pm
LEFT JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL;

The same pattern works for comment metadata and term relationships:

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

-- Term relationships pointing to deleted posts
DELETE tr
FROM wp_term_relationships tr
LEFT JOIN wp_posts p ON p.ID = tr.object_id
WHERE p.ID IS NULL;

Be careful with that last query on sites that attach terms to things other than posts. Some plugins use wp_term_relationships for links or custom objects. If you are not sure, skip it.

Run these through wp db query, replacing wp_ with your prefix. On sites with a custom prefix, a small PHP script run with wp eval-file is safer, because $wpdb fills in the correct table names automatically:

// cleanup-orphans.php  (run with: wp eval-file cleanup-orphans.php)
<?php
global $wpdb;

$deleted = $wpdb->query(
"DELETE pm FROM {$wpdb->postmeta} pm
LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id
WHERE p.ID IS NULL"
);

WP_CLI::success( sprintf( 'Deleted %d orphaned postmeta rows.', (int) $deleted ) );

Step 6: Fix Oversized Autoloaded Options

This is the cleanup with the biggest effect on performance. Options marked to autoload are fetched together on every request, before any page logic runs. A few kilobytes is normal. Several megabytes slows every uncached page and the entire admin.

WordPress 6.6 changed how autoload is stored. Instead of only yes and no, the autoload column can now contain:

Value	Meaning
on	Explicitly set to autoload
off	Explicitly set not to autoload
auto	No explicit choice; WordPress decides, and currently autoloads it
auto-on	WordPress decided to autoload it
auto-off	WordPress decided not to, for example because the value is large
yes, no	Legacy values from before 6.6, still honored

WordPress 6.6 also stops autoloading new large options by default when the code did not ask for autoloading explicitly, and Tools → Site Health flags a critical issue when autoloaded options total more than about 800 KB.

Measure the total size of autoloaded options:

-- Total size of autoloaded options, in KB
SELECT ROUND(SUM(LENGTH(option_value)) / 1024) AS autoload_kb, COUNT(*) AS options
FROM wp_options
WHERE autoload IN ('yes', 'on', 'auto-on', 'auto');

Then find the largest ones:

-- Largest autoloaded options
SELECT option_name, ROUND(LENGTH(option_value) / 1024, 1) AS size_kb, autoload
FROM wp_options
WHERE autoload IN ('yes', 'on', 'auto-on', 'auto')
ORDER BY LENGTH(option_value) DESC
LIMIT 25;

For each large option, look at the name prefix and identify which plugin or theme owns it. Then decide:

• It belongs to a plugin you no longer use. Delete it with wp option delete <option_name>.
• It belongs to an active plugin, but is not needed on every page, such as a cached API response or an import log. Turn off autoloading.
• It belongs to an active plugin and is needed everywhere, such as core settings. Leave it, and consider reporting the size to the plugin author.

To turn off autoloading for an option without changing its value, use the core function added in WordPress 6.4:

# Terminal
wp eval 'wp_set_option_autoload( "some_plugin_big_cache", false );'

Or for several options at once:

# Terminal
wp eval 'wp_set_options_autoload( array( "plugin_a_log", "plugin_b_cache" ), false );'

Using the API rather than an UPDATE query keeps the object cache in sync. Afterward, re-run the size query and check Site Health.

Step 7: Optimize the Tables

After deleting a lot of rows, tables keep the free space allocated. Optimizing rebuilds them and reclaims it:

# Terminal
wp db optimize

wp db optimize runs mysqlcheck --optimize against the WordPress database. On InnoDB, the default storage engine for modern MySQL and MariaDB, optimizing a table rebuilds it, which can lock it briefly and needs free disk space roughly equal to the table's size. Run it during a quiet period on large sites.

You can also optimize individual tables with SQL:

OPTIMIZE TABLE wp_posts, wp_postmeta, wp_options;

Optimizing does not make queries dramatically faster on its own. Its main benefit is reclaiming disk space and keeping backups smaller after a big cleanup. Running it every week does nothing useful; after large deletions is enough.

To repair tables that report errors:

# Terminal
wp db check
wp db repair

Step 8: Keep the Database Lean

Cleanup is a one-off. These settings stop the junk from coming back:

// wp-config.php
define( 'WP_POST_REVISIONS', 10 );   // Keep the last 10 revisions per post
define( 'AUTOSAVE_INTERVAL', 120 );  // Autosave every 2 minutes instead of 1
define( 'EMPTY_TRASH_DAYS', 7 );     // Empty trash after 7 days

• Make sure cron runs reliably. Scheduled cleanup tasks, such as deleting old auto-drafts and expired transients, depend on WP-Cron. See how to replace WP-Cron with a real server cron job.
• Set retention limits in logging, security, form, and analytics plugins.
• Delete plugins properly. Deactivating keeps data. Deleting from the Plugins screen runs the plugin's uninstall routine, if it has one.
• Install a persistent object cache on busy sites so transients do not live in wp_options.
• Review every few months: table sizes, autoload size, and Site Health.

If you prefer a plugin for routine cleanup, tools such as WP-Optimize and Advanced Database Cleaner offer scheduled cleanups of revisions, transients, and spam from a settings screen. Use them with the same caution: back up first and review what they will delete. For a broader maintenance checklist, see the ultimate guide to WordPress maintenance.

Common Problems and Fixes

• Argument list too long when deleting posts. There are too many IDs for one command. Delete in batches with --posts_per_page=500 in a loop, as shown above.
• SQL query fails with Table 'db.wp_posts' doesn't exist. The site uses a different table prefix. Check with wp db prefix.
• Site settings disappeared after deleting options. An option belonged to an active plugin or theme. Restore it from the backup, or restore the whole database if several options are affected.
• wp db optimize fails or the disk fills up. InnoDB rebuilds need temporary space. Free disk space, or optimize the largest tables one at a time during a quiet period.
• The database grows back within weeks. A plugin is logging aggressively or cron is not running, so scheduled cleanups never happen. Check plugin log tables and cron.
• Autoload size is still high after cleanup. A large option is set to on by an active plugin. Turn off autoloading with wp_set_option_autoload() if the plugin does not need it on every page, or replace the plugin.
• Action Scheduler tables are huge. WooCommerce and other plugins store completed jobs in wp_actionscheduler_actions. They are purged automatically after 30 days by default if cron runs; past-due and failed jobs can be reviewed under WooCommerce → Status → Scheduled Actions, or Tools → Scheduled Actions when Action Scheduler is used without WooCommerce.

WordPress Database Cleanup FAQ

Conclusion

A cluttered WordPress database rarely announces itself, but it shows up as slower admin screens, slower uncached pages, and backups that keep getting bigger. The cleanup itself is methodical: back up, measure table sizes, delete old revisions, auto-drafts, trash, spam, and expired transients, remove orphaned metadata, bring autoloaded options under control, and optimize the tables you shrank.

The autoload step usually matters most for speed, so give it the most attention, and use wp_set_option_autoload() instead of raw SQL for changes. Then keep the gains with revision limits, a shorter trash period, reliable cron, and plugins that do not log forever. A few minutes every quarter is enough to keep the database lean.

Here are some useful references for going deeper on WordPress database maintenance:

1. WP-CLI Commands: wp db — export, import, query, size, optimize, check, and repair.
2. Make WordPress Core: Options API: Disabling autoload for large options — the WordPress 6.6 autoload changes and Site Health check.
3. WordPress Developer Resources: wp_set_option_autoload() — changing an option's autoload value safely.
4. WordPress Developer Resources: Optimization — the official overview of WordPress performance, including database tuning.
5. MySQL Reference Manual: OPTIMIZE TABLE Statement — how table optimization works on InnoDB.