WordPress Database Optimization: Clean Up Bloat and Speed Up Queries

A WordPress database accumulates junk over time: post revisions, trashed items, expired transients, orphaned plugin data, and spam comments. On a site that's been running for several years with active editing and regular plugin churn, the overhead is substantial. Post revisions alone can run into the tens of thousands of rows. Regular database cleanup is straightforward, makes a measurable difference to admin performance and backup sizes, and takes less time to do properly than most people expect.

What's taking up space

Post revisions are the single largest source of database bloat on most sites. WordPress saves a new revision entry in wp_posts every time you save a post or page, with corresponding rows in wp_postmeta. A page edited 50 times has 50 revision copies stored. For a site with hundreds of posts that have been heavily edited over several years, revisions can represent tens of thousands of rows and hundreds of megabytes of data.

Auto-drafts are created automatically when you open the post editor and haven't yet published or manually saved. Abandoned sessions leave these behind. The wp_posts table accumulates auto-draft entries indefinitely unless you explicitly delete them.

Expired transients are temporary cached values stored in wp_options. Every transient creates two rows: the value and its expiration timestamp. Many plugins store transients but don't clean them up reliably when they expire. The wp_options table can fill with thousands of orphaned transient rows, causing the option loading query (which WordPress runs on every page load) to become increasingly slow.

Orphaned plugin data is metadata left behind by deactivated and deleted plugins in wp_options, wp_postmeta, and wp_usermeta. Most plugins don't clean up on deactivation (since the data might be needed on re-activation), and many don't clean up on deletion either. After years of plugin churn, this residual data serves no function.

Limit revisions going forward

Before cleaning up existing revisions, limit how many WordPress creates going forward. Add this to wp-config.php:

define( 'WP_POST_REVISIONS', 5 );

This keeps a maximum of 5 revisions per post, discarding older ones automatically. Set it to false to disable revisions entirely. Disabling them completely is fine for sites where content is managed by a small team; keeping 3–5 is a sensible safety net for sites with multiple content editors who occasionally need to recover earlier drafts. This change only affects new revisions, it doesn't delete existing ones.

Cleanup via WP-CLI

WP-CLI is the fastest way to clean a database if your host provides access. These commands handle the most common bloat categories:

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

# Delete expired transients
wp transient delete --expired

# Empty trash (posts, pages, custom post types)
wp post delete $(wp post list --post_status=trash --format=ids) --force

# Delete spam and trashed comments
wp comment delete $(wp comment list --status=spam --format=ids)
wp comment delete $(wp comment list --status=trash --format=ids)

# Run OPTIMIZE TABLE on all tables
wp db optimize

Take a database backup before running these commands. They're not reversible via WP-CLI. The wp db optimize command runs OPTIMIZE TABLE on every table, reclaiming space fragmented by deletions and rebuilding table indexes. On large tables that haven't been optimized in years, this can reduce table size significantly and improve query speed.

Cleanup via plugin

WP-Optimize is the most widely used plugin for database maintenance. It provides a clean interface to delete revisions, auto-drafts, spam and trashed comments, expired transients, and orphaned metadata. It can also run OPTIMIZE TABLE on individual tables and schedule automatic cleanup runs.

Always take a backup before running a cleanup operation through WP-Optimize. The deletion operations are permanent and not reversible within the plugin. Most managed hosting environments include automated daily backups, verify yours is running before you start cleaning.

Advanced Database Cleaner is an alternative with more granular control, particularly useful for identifying and removing orphaned options from specific deleted plugins. If your wp_options table is unusually large (over 10MB is a warning sign), ADC's orphaned options scanner is the most efficient way to identify what's causing it.

Investigating slow queries with Query Monitor

Database cleanup reduces table size and speeds up full-table scans, but it doesn't identify queries that are slow by design, missing indexes, inefficient WHERE clauses, or N+1 query patterns where a loop generates one database query per iteration. For those, you need Query Monitor.

Install Query Monitor and load a slow page. The Database Queries panel shows every query executed, its execution time, and which plugin or theme generated it. Look for queries with execution times above 50ms (anything above 100ms is a serious problem), duplicate queries (the same query run multiple times per page load), and queries without indexes (shown by a high "Rows" count relative to results).

A well-optimized WordPress page should make under 30 database queries. Pages with 100+ queries are doing excessive work, usually caused by poorly-coded plugins loading per-post metadata in loops rather than batching queries.

Object caching for sustained query performance

Database cleanup is a one-time improvement to table health. Object caching is an ongoing performance multiplier that prevents redundant queries from hitting the database at all. Redis or Memcached stores the results of frequent database queries in memory. Subsequent requests for the same data read from memory rather than executing a database query.

The combination of a cleaned, well-indexed database and a Redis object cache produces compounding improvements: the database runs efficient queries, and the cache means most of those queries only run once per cache lifetime rather than on every page load. If your host offers Redis, enabling it alongside database cleanup is the highest-ROI combination available for dynamic WordPress performance.