WordPress Database Optimization - The Complete Guide

A fresh WordPress install has a small, fast database. After a year of active use - especially with multiple plugins, a busy comments section, and an active content team - the database can bloat to many times its necessary size. This bloat slows queries, increases backup sizes, and in extreme cases causes timeouts.

This guide explains what causes database bloat, how to measure it, how to clean it safely, and how to keep it under control long-term.

What causes WordPress database bloat

Post revisions

Every time you save a post, WordPress creates a revision. A post saved 50 times has 50 revision records in wp_posts. On a site with thousands of posts, this can represent tens of thousands of rows of near-duplicate content.

Check how many revisions you have:

SELECT COUNT(*) FROM wp_posts WHERE post_status = 'inherit' AND post_type = 'revision';

Auto-drafts

WordPress creates auto-draft entries as you work in the editor. These accumulate unless explicitly deleted.

SELECT COUNT(*) FROM wp_posts WHERE post_status = 'auto-draft';

Trashed posts

Posts in the trash stay in the database. WordPress deletes them after 30 days by default, but if EMPTY_TRASH_DAYS is set to 0 (disable trash), they are deleted immediately on trash action - which means they never accumulate. If trash is enabled and old, they pile up.

SELECT COUNT(*) FROM wp_posts WHERE post_status = 'trash';

Transients

Transients are temporary cached values stored in the database. Plugins use them heavily - for caching API responses, computed values, and query results. When a transient expires, WordPress marks it as expired but does not always clean it up immediately. A database full of expired transients is common.

SELECT COUNT(*) FROM wp_options WHERE option_name LIKE '_transient_%';
SELECT COUNT(*) FROM wp_options WHERE option_name LIKE '_site_transient_%';

If you are using Redis or Memcached as the object cache, transients are stored there instead of the database, and this table will stay clean automatically.

Orphaned postmeta

When a post is deleted, WordPress should delete its associated wp_postmeta rows. Plugins that add custom meta fields sometimes do not clean up after themselves. Over years of adding and removing plugins, the wp_postmeta table can have millions of orphaned rows.

SELECT COUNT(*)
FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL;

Spam and unapproved comments

Comment spam accumulates in wp_comments. Even if comments are auto-rejected, the records often stay in the database.

SELECT comment_approved, COUNT(*)
FROM wp_comments
GROUP BY comment_approved;

Values of spam and 0 (pending) can accumulate to hundreds of thousands of rows on unprotected sites.

How to check database size

Check the size of each table in MySQL to identify where the bloat is:

SELECT
    table_name AS 'Table',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC;

On a typical WordPress site, the largest tables are usually:

wp_posts - grows with revisions and old content
wp_postmeta - grows with plugin metadata
wp_options - grows with transients and plugin settings
wp_comments - grows with spam

If wp_options is over 10MB, you almost certainly have transient bloat or plugin settings that are not being cleaned up.

Cleaning the database safely

Always take a backup before any cleanup. Database operations are irreversible.

Method 1 - WP-CLI (recommended for command-line access)

WP-CLI has a database optimization command:

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

# Delete auto-drafts
wp post delete $(wp post list --post_status='auto-draft' --fields='ID' --format=ids) --force

# Delete trashed posts
wp post delete $(wp post list --post_status='trash' --fields='ID' --format=ids) --force

# Delete expired transients
wp transient delete --expired

# Or delete ALL transients (if you have object cache configured)
wp transient delete --all

For orphaned postmeta:

wp db query "DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts p ON pm.post_id = p.ID WHERE p.ID IS NULL"

For spam comments:

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

Method 2 - WP-Optimize plugin

WP-Optimize provides a UI for all the above operations. It shows you exactly what it will delete before running. Good for sites where you do not have WP-CLI access or want a visual overview.

The plugin can also run cleanup on a schedule - useful for "set it and forget it" maintenance.

Method 3 - Direct SQL

For large cleanups where WP-CLI times out, run SQL directly through phpMyAdmin or MySQL CLI:

-- Delete all revisions
DELETE FROM wp_posts WHERE post_type = 'revision';

-- Delete all auto-drafts
DELETE FROM wp_posts WHERE post_status = 'auto-draft';

-- Delete all trashed posts
DELETE FROM wp_posts WHERE post_status = 'trash';

-- Delete all expired transients
DELETE FROM wp_options
WHERE option_name LIKE '_transient_%'
  AND option_name LIKE '%_transient_timeout_%'
  AND option_value < UNIX_TIMESTAMP();

DELETE FROM wp_options
WHERE option_name LIKE '_transient_%'
  AND REPLACE(option_name, '_transient_timeout_', '_transient_') NOT IN (
    SELECT option_name FROM wp_options WHERE option_name LIKE '_transient_timeout_%'
  );

-- Clean orphaned postmeta
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL;

-- Delete spam and trash comments
DELETE FROM wp_comments WHERE comment_approved = 'spam';
DELETE FROM wp_comments WHERE comment_approved = 'trash';

Optimise the database tables

After cleaning, the tables have gaps where deleted rows used to be. Optimise them to reclaim the space and defragment:

# WP-CLI
wp db optimize

# MySQL directly
mysqlcheck -u [user] -p --optimize [database_name]

Or in SQL:

OPTIMIZE TABLE wp_posts, wp_postmeta, wp_options, wp_comments;

OPTIMIZE TABLE on InnoDB tables rebuilds the table and reclaims disk space. On large tables, it can take several minutes and briefly locks the table. Run during low-traffic periods.

Prevent future bloat

Limit post revisions in wp-config.php

define( 'WP_POST_REVISIONS', 5 );  // keep only 5 revisions per post

Set this before the site accumulates thousands of posts. On an existing site, add this constant and then run a one-time cleanup of old revisions.

Use an external object cache

Install Redis or Memcached and configure WordPress to use it. With an object cache active, transients are stored in memory instead of the database. The wp_options table stays clean and transient lookups are faster.

On Kinsta, Redis is available as a $100/month add-on toggled from the dashboard. On Cloudways, enable Memcached from the server settings — it's included. WP Engine does not support Redis; it uses proprietary server-level caching (Nginx + EverCache) instead of a standard object cache.

Schedule regular automated cleanup

WP-Optimize can run cleanup on a schedule. Set it up once and forget it:

Delete expired transients: daily
Delete spam comments: weekly
Delete revisions beyond the limit: weekly

Audit plugin usage annually

Plugins are the biggest source of database bloat over time. An abandoned plugin leaves its database tables and option entries behind even after deactivation. Annually, list all installed plugins and ask: is this still being used? If not, deactivate and delete it, then clean up any leftover data.

Expected results

On a site with 2 years of WordPress activity:

Removing revisions: typically 30-60% reduction in wp_posts size
Removing transients: wp_options can shrink from 50MB+ to under 1MB
Removing orphaned postmeta: wp_postmeta can shrink 40-70% on plugin-heavy sites
Total database size reduction: 50-80% is common on neglected sites

After cleanup, run your benchmarks again. On shared hosting, TTFB improvements of 100-300ms are typical when a bloated wp_options table is cleaned up.