How to clean up and optimize the WordPress database?

To clean up and optimize the WordPress database, you first take a full backup, then remove unnecessary data like old post revisions, auto-drafts, trashed posts, spam comments, expired transients, and leftover tables from deleted plugins, and finally run an optimize command on the tables to reclaim space. You can do this with a plugin like WP-Optimize or Advanced Database Cleaner, with WP-CLI commands, or with carefully written SQL in phpMyAdmin.

Over time, every WordPress database collects clutter. It rarely breaks anything on its own, but it can make backups bigger, slow down queries, and bloat the data WordPress loads on every page. In this guide, you'll learn what builds up in your database, how to clean it safely using three different methods, how to prevent it from growing back, and when optimization actually makes a noticeable difference.

Why Your WordPress Database Gets Bloated

WordPress stores all your content and settings in a MySQL or MariaDB database. As you publish, edit, install plugins, and receive comments, data piles up that you no longer need:

• Post revisions: Every time you save a post or page, WordPress stores a revision in wp_posts. A post edited 50 times can have 50 revisions, each a full copy of the content.
• Auto-drafts: WordPress creates an auto-draft whenever you open the editor. Most are cleaned up automatically after about a week, but some linger.
• Trashed content: Posts, pages, and comments in the trash stay in the database until they're permanently deleted, which happens after 30 days by default.
• Spam and unapproved comments: Spam comments can number in the thousands on sites without good filtering.
• Expired transients: Plugins store temporary cached data in wp_options. Expired ones are removed over time, but some can build up.
• Orphaned metadata: Rows in wp_postmeta, wp_commentmeta, and wp_termmeta that belong to content that no longer exists.
• Leftover plugin data: Many plugins don't delete their tables and options when you uninstall them.
• Large autoloaded options: Settings that WordPress loads on every page request, which can grow large if plugins misbehave.
• Logs: Security, redirection, email, and analytics plugins may store log entries in custom tables indefinitely.

Does Database Optimization Actually Improve Speed?

It's worth setting realistic expectations. Cleaning up a small blog with a few hundred revisions won't make a visible difference to page speed, especially if you already use page caching.

Database cleanup makes a real difference when:

• Your autoloaded options are large, since they're loaded on every uncached request.
• Tables like wp_postmeta or wp_options have grown to hundreds of thousands or millions of rows.
• Plugin log tables have grown to hundreds of megabytes or more.
• The admin dashboard, search, or WooCommerce order screens feel slow.
• Backups and migrations are taking much longer than they should.

Even when the speed gain is small, a lean database is easier to back up, restore, and move.

Step 1: Back Up Your Database First

Cleaning the database means permanently deleting data. There's no undo button. Before you do anything, make a full backup and confirm you can download it.

With WP-CLI, one command exports your entire database:

wp db export backup-$(date +%Y-%m-%d).sql

You can also use your host's backup tool, phpMyAdmin's Export tab, or a backup plugin like UpdraftPlus. If you have a staging site, it's even better to test your cleanup there first.

Step 2: Check How Big Your Database Is

Knowing where the weight is helps you focus your effort.

With WP-CLI:

# Total database size
wp db size --human-readable

# Size of each table, largest first
wp db size --tables --human-readable --orderby=size --order=desc

In phpMyAdmin, select your database and look at the Size column in the table list. In the WordPress dashboard, go to Tools > Site Health > Info > Database for some basic details, and check Tools > Site Health > Status for any warnings about autoloaded options.

Pay special attention to wp_posts, wp_postmeta, wp_options, wp_comments, and any large tables with prefixes you don't recognize.

Method 1: Clean Up With a Plugin

For most people, a plugin is the easiest and safest way to clean up the database.

Using WP-Optimize

WP-Optimize is a popular free plugin that handles most cleanup tasks with a few clicks:

1. Install and activate: Go to Plugins > Add New Plugin, search for "WP-Optimize," then install and activate it.
2. Open the database screen: Go to WP-Optimize > Database.
3. Review the options: You'll see a list of cleanup tasks, such as cleaning post revisions, auto-drafts, trashed posts, spam and trashed comments, expired transients, and orphaned metadata, along with a count of how many items each will remove.
4. Select what to clean: Tick the tasks you want. Start with the safe ones: revisions, auto-drafts, spam comments, trashed items, and expired transients.
5. Run the optimization: Click Run all selected optimizations.
6. Optimize tables: Use the Tables tab to optimize individual tables and spot tables left behind by removed plugins.
7. Schedule cleanups: Under Settings, you can schedule automatic cleanups weekly or monthly, and choose to keep a certain number of recent revisions.

Using Advanced Database Cleaner

Advanced Database Cleaner is another well-known option. Its standout feature is identifying orphaned tables, options, and cron events left by deleted plugins. It lists them so you can review and delete leftovers with more confidence.

Other plugins such as WP-Sweep and the database tools included in WP Rocket and LiteSpeed Cache offer similar cleanup options. You only need one.

A Word of Caution About Orphaned Data

Be careful when deleting "orphaned" tables or options. Plugins can't always tell whether a table belongs to an active plugin, a must-use plugin, or a feature your host added. If you don't recognize a table, search its name online or check it against your installed plugins before deleting it.

Method 2: Clean Up With WP-CLI

If you have SSH access, WP-CLI is fast, precise, and easy to automate. Here are the most useful commands.

Delete Post Revisions

# Count revisions
wp post list --post_type=revision --format=count

# Delete all revisions
wp post delete $(wp post list --post_type=revision --format=ids) --force

On large sites with tens of thousands of revisions, the list of IDs can be too long for one command. In that case, process them in batches:

wp post list --post_type=revision --format=ids --posts_per_page=500 | xargs -r wp post delete --force

Run it repeatedly until the count reaches zero.

Empty the Trash and Remove Auto-Drafts

wp post delete $(wp post list --post_status=trash --post_type=any --format=ids) --force
wp post delete $(wp post list --post_status=auto-draft --post_type=any --format=ids) --force

Delete 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

# Delete only expired transients
wp transient delete --expired

# Or delete all transients (they will be rebuilt as needed)
wp transient delete --all

Optimize the Tables

wp db optimize

This runs mysqlcheck --optimize on your database tables. On InnoDB tables, which is the default for modern WordPress installs, it rebuilds the table and reclaims unused space.

If any of these commands report "Too few arguments" or similar, it usually just means there was nothing to delete.

Method 3: Clean Up With SQL in phpMyAdmin

If you're comfortable with SQL, you can run cleanup queries directly in phpMyAdmin's SQL tab. Replace wp_ with your actual table prefix, and make sure your backup is ready.

Delete Post Revisions and Their Metadata

DELETE pm
FROM wp_postmeta AS pm
INNER JOIN wp_posts AS p ON pm.post_id = p.ID
WHERE p.post_type = 'revision';

DELETE FROM wp_posts
WHERE post_type = 'revision';

Running the postmeta delete first ensures you don't leave orphaned metadata behind. Revisions don't usually have term relationships, but if you want to be thorough, you can clean those up with the orphan queries below.

Delete Spam Comments and Their Metadata

DELETE cm
FROM wp_commentmeta AS cm
INNER JOIN wp_comments AS c ON cm.comment_id = c.comment_ID
WHERE c.comment_approved = 'spam';

DELETE FROM wp_comments
WHERE comment_approved = 'spam';

Remove Orphaned Metadata

-- Postmeta for posts that no longer exist
DELETE pm
FROM wp_postmeta AS pm
LEFT JOIN wp_posts AS p ON pm.post_id = p.ID
WHERE p.ID IS NULL;

-- Commentmeta for comments that no longer exist
DELETE cm
FROM wp_commentmeta AS cm
LEFT JOIN wp_comments AS c ON cm.comment_id = c.comment_ID
WHERE c.comment_ID IS NULL;

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

Be careful with that last query if you still use the old Link Manager, since link objects also use wp_term_relationships. Most sites don't.

Delete Expired Transient

DELETE a, b
FROM wp_options AS a
INNER JOIN wp_options AS b
    ON b.option_name = CONCAT(
        '_transient_timeout_',
        SUBSTRING(a.option_name, CHAR_LENGTH('_transient_') + 1)
    )
WHERE a.option_name LIKE '\_transient\_%'
  AND a.option_name NOT LIKE '\_transient\_timeout\_%'
  AND b.option_value < UNIX_TIMESTAMP();

This removes each expired transient together with its timeout row. Honestly, wp transient delete --expired or a plugin is simpler, but it's useful to see what's happening under the hood.

Optimize Tables

In phpMyAdmin, tick the tables you want, then choose Optimize table from the With selected dropdown. Or run:

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

Step 3: Tame Autoloaded Options

Autoloaded options deserve special attention, because they're read on every uncached page load. Here's how to find the biggest ones:

SELECT option_name, LENGTH(option_value) AS size_bytes, autoload
FROM wp_options
WHERE autoload IN ('yes', 'on', 'auto-on', 'auto')
ORDER BY size_bytes DESC
LIMIT 20;

Look through the results. Common culprits include options from plugins you've already deleted, large cached data blobs, and old log entries stored as options.

• If the plugin is gone: You can usually delete the option. Use wp option delete option_name so WordPress clears its caches too.
• If the plugin is active: Don't delete the option. Instead, check the plugin's settings for a way to clear its cache or logs, or contact its developer.
• If an option doesn't need to autoload: Since WordPress 6.4, you can change autoload status with wp_set_option_autoload() in code. WP-CLI also supports wp option set-autoload option_name no in recent versions.

WordPress 6.6 also stopped autoloading very large options by default, which helps newer sites, but older installs can still carry large legacy options.

Step 4: Remove Leftover Plugin Tables

Compare your list of tables with the plugins you actually use:

wp db tables --all-tables-with-prefix

Tables with names like wp_oldplugin_logs for a plugin you uninstalled long ago are candidates for removal. Once you're sure, you can drop them:

DROP TABLE IF EXISTS wp_oldplugin_logs;

Double-check before running DROP TABLE. It's permanent, and your backup is your only way back.

How to Prevent Database Bloat

Cleaning is good. Not needing to clean is better. A few settings keep things lean.

Limit Post Revisions

Add this to wp-config.php, above the line that says /* That's all, stop editing! Happy publishing. */:

define( 'WP_POST_REVISIONS', 10 );

This keeps the 10 most recent revisions per post, which is plenty for most editors. Setting it to false disables revisions entirely, but I don't recommend that, since revisions can save you from a bad edit.

For finer control, you can limit revisions per post type with a filter in a custom plugin or child theme's functions.php:

function sajjad_limit_revisions_by_type( $num, $post ) {
    if ( 'page' === $post->post_type ) {
        return 5;
    }

    return $num;
}
add_filter( 'wp_revisions_to_keep', 'sajjad_limit_revisions_by_type', 10, 2 );

Empty the Trash Sooner

The default trash period is 30 days. You can shorten it in wp-config.php:

define( 'EMPTY_TRASH_DAYS', 7 );

Other Good Habits

• Use effective spam protection: Akismet or Antispam Bee stop spam from piling up in the first place.
• Choose plugins that clean up after themselves: Look for an option like "delete all data on uninstall" in plugin settings.
• Delete plugins instead of just deactivating them: Deactivated plugins can still leave scheduled events and autoloaded options.
• Limit log retention: Set security, email, and redirection logs to keep only a few weeks or months of data.
• Schedule regular cleanups: Use WP-Optimize's scheduler or a monthly WP-CLI cron job.

How Often Should You Optimize?

For most blogs and business sites, a light cleanup once a month or once a quarter is enough. Busy WooCommerce stores, membership sites, and sites with large editorial teams may benefit from monthly scheduled cleanups. There's no need to optimize daily, and doing so on large tables can briefly lock them during busy periods, so run heavy optimizations during low-traffic hours.

---

FAQ: Cleaning Up the WordPress Database

---

Conclusion

Cleaning up and optimizing your WordPress database is mostly about removing things you no longer need: old revisions, spam, trash, expired transients, orphaned metadata, and tables left behind by old plugins. Whether you use WP-Optimize, WP-CLI, or SQL, the process is the same. Back up first, measure where the bloat is, clean the safe items, review anything unfamiliar carefully, and then optimize your tables.

After that, prevention does most of the work. Limit revisions, shorten the trash period, keep spam under control, and schedule a regular cleanup. A lean database keeps your backups small, your admin dashboard snappy, and your site ready to grow without dragging extra weight along with it.