WordPress database optimization: how to clean up and speed up your database

Every WordPress site accumulates database bloat over time. Post revisions pile up, expired transients linger, spam comments sit in the trash, abandoned plugin tables remain after uninstallation, and session data from months ago takes up space. None of this data serves a purpose, but MySQL still reads through it when processing queries.

A bloated database does not just waste disk space. It slows down queries, increases backup sizes, makes migrations take longer, and can push you into MySQL memory limits that cause intermittent errors. The good news is that WordPress database cleanup is straightforward once you know what to remove and what to leave alone.

Why WordPress databases grow#

WordPress stores everything in a single MySQL database – posts, pages, comments, settings, plugin data, user metadata, and transient cache values. Over time, several types of data accumulate without any automatic cleanup.

Post revisions

Every time you save a draft or update a published post, WordPress creates a revision. A post edited 50 times has 50 revision records in wp_posts , each with its own set of rows in wp_postmeta . For a site with 200 posts that average 20 revisions each, that is 4,000 additional rows in wp_posts alone, plus potentially tens of thousands of rows in wp_postmeta .

WordPress keeps revisions indefinitely by default. There is no automatic pruning.

Transients

Transients are temporary cached values that plugins and themes store in the database. They have an expiration time, but WordPress does not proactively delete expired transients. They sit in the wp_options table until something explicitly requests them (at which point WordPress checks the expiration and deletes if expired) or until you clean them up manually.

A site with 20 active plugins can easily accumulate thousands of expired transient rows in wp_options . Since wp_options is loaded on every page request, this directly affects performance.

Spam and trashed comments

WordPress moves deleted comments to the trash and keeps spam comments for review. Neither is automatically purged. A site that receives 500 spam comments per day has 180,000 spam comment rows after a year – each with metadata in wp_commentmeta .

Orphaned metadata

When a post, comment, or user is deleted, WordPress removes the main record but does not always clean up the associated metadata. wp_postmeta rows that reference deleted posts, wp_commentmeta rows for deleted comments, and wp_usermeta rows for deleted users all remain in the database doing nothing.

Poorly written plugins are the biggest source of orphaned metadata. A plugin that stores 15 custom fields per post but does not clean them up on post deletion leaves 15 orphaned wp_postmeta rows for every deleted post.

Expired sessions

WordPress stores user session tokens in wp_usermeta . When a session expires (after 48 hours for regular users, 14 days for “remember me”), the token data remains in the database until the user logs in again or the tokens are manually cleaned. Sites with many registered users (membership sites, WooCommerce stores) accumulate significant session data.

Abandoned plugin tables

Many plugins create their own database tables. When you uninstall a plugin, WordPress runs the plugin’s uninstall hook – but not all plugins implement one. The tables remain in the database after the plugin is gone. Some plugins create dozens of tables. A WooCommerce store that tried and removed five analytics plugins might have 40 orphaned tables sitting in the database.

Autoloaded options

The wp_options table has an autoload column. Rows marked autoload = yes are loaded into memory on every single page request, regardless of whether they are needed. Plugins that store large serialized arrays with autoload enabled cause every page load to pull unnecessary data into memory. This is one of the most impactful performance issues in WordPress databases, and one of the hardest to notice without looking directly at the data.

What to clean up#

Limit post revisions

You do not need 50 revisions of every post. Add this to wp-config.php to limit future revisions:

define('WP_POST_REVISIONS', 5);

This keeps the 5 most recent revisions for each post. Setting it to 0 disables revisions entirely, but that removes your safety net for accidental changes – 5 to 10 revisions is a reasonable balance.

To clean up existing excess revisions with WP-CLI:

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

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

For large sites with thousands of revisions, do this in batches to avoid hitting memory limits:

# Delete 500 revisions at a time
wp post list --post_type=revision --field=ID --posts_per_page=500 | xargs wp post delete --force

Delete expired transients

# Delete all expired transients
wp transient delete --expired

# See how many transients exist total
wp transient list --format=count

# Nuclear option: delete ALL transients (they will regenerate as needed)
wp transient delete --all

Deleting all transients is safe – they are cache values that plugins regenerate on demand. The first page load after clearing them will be slightly slower as plugins rebuild their cached data, but everything will work.

Purge spam and trashed comments

# Delete all spam comments
wp comment delete $(wp comment list --status=spam --field=ID) --force

# Delete all trashed comments
wp comment delete $(wp comment list --status=trash --field=ID) --force

Clean up orphaned metadata

This requires direct database queries. Back up your database first.

# Orphaned postmeta (references deleted posts)
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;"

# Orphaned commentmeta (references deleted comments)
wp db query "DELETE cm FROM wp_commentmeta cm LEFT JOIN wp_comments c ON cm.comment_id = c.comment_ID WHERE c.comment_ID IS NULL;"

# Orphaned usermeta (references deleted users)
wp db query "DELETE um FROM wp_usermeta um LEFT JOIN wp_users u ON um.user_id = u.ID WHERE u.ID IS NULL;"

Adjust the table prefix if yours is not wp_ .

Find and fix autoloaded bloat

Check how much data is autoloaded on every request:

wp db query "SELECT SUM(LENGTH(option_value)) AS autoload_size FROM wp_options WHERE autoload = 'yes';" --skip-column-names

If this returns a number over 1 MB (1,000,000 bytes), you have autoload bloat. Find the biggest offenders:

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

Common autoload offenders:

• _transient_*  and  _site_transient_*  rows (should not be autoloaded, but some plugins set them incorrectly)
• Deactivated plugin settings that are still autoloaded
• Analytics or logging data stored in options instead of custom tables

For options that belong to plugins you have removed, it is safe to delete them:

wp db query "DELETE FROM wp_options WHERE option_name LIKE 'removed_plugin_prefix_%';"

For active plugins with large autoloaded options, you can disable autoloading without deleting the data:

wp db query "UPDATE wp_options SET autoload = 'no' WHERE option_name = 'large_option_name';"

Be careful with this – some options need to be autoloaded for the plugin to function. Test after making changes.

What not to touch#

Do not delete core WordPress options. The wp_options table contains critical site configuration ( siteurl , home , blogname , active_plugins , current_theme , etc.). Deleting these breaks WordPress.

Do not delete tables you do not recognize. An unfamiliar table might belong to an active plugin or theme. Check the table prefix – if it matches a plugin you are using (e.g., wp_woocommerce_* , wp_yoast_* ), leave it alone.

Do not optimize in production during peak hours. MySQL’s OPTIMIZE TABLE locks InnoDB tables briefly during the operation. On a busy site, this can cause requests to queue up. Run optimization during low-traffic periods.

Do not delete user sessions if users are actively logged in. Clearing session tokens logs out every user immediately. On a WooCommerce store, this clears active carts.

Running MySQL optimize#

MySQL tables fragment over time as rows are inserted, updated, and deleted. The OPTIMIZE TABLE command defragments tables and reclaims unused space.

Using WP-CLI:

wp db optimize

This runs OPTIMIZE TABLE on every WordPress table.

For specific tables:

wp db query "OPTIMIZE TABLE wp_posts, wp_postmeta, wp_options, wp_comments, wp_commentmeta;"

Focus on the tables that change most frequently – wp_posts , wp_postmeta , wp_options , and wp_comments are typically the most fragmented.

InnoDB vs MyISAM

WordPress has used InnoDB as its default storage engine since version 4.2. InnoDB handles concurrent reads and writes better than MyISAM, supports transactions, and recovers from crashes without manual repair. If you see MyISAM tables in your WordPress database, they are either from a very old installation or from a plugin that explicitly chose MyISAM.

Check your table engines:

wp db query "SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE();"

If any WordPress tables are still MyISAM, convert them:

wp db query "ALTER TABLE wp_posts ENGINE = InnoDB;"
wp db query "ALTER TABLE wp_postmeta ENGINE = InnoDB;"

The OPTIMIZE TABLE command behaves differently for each engine:

• MyISAM: Defragments the data file and rebuilds indexes. This physically reclaims disk space.
• InnoDB: Rebuilds the table by creating a new copy and swapping it in. It reclaims space but requires temporary disk space equal to the table size.

For InnoDB tables, OPTIMIZE TABLE is less critical than for MyISAM because InnoDB manages its space more efficiently. Running it quarterly is sufficient for most sites.

Scheduling regular cleanup#

Manual cleanup works but is easy to forget. Set up automated cleanup using WP-CLI and cron.

Create a cleanup script:

#!/bin/bash
# wordpress-db-cleanup.sh

WP_PATH="/path/to/wordpress"

# Delete expired transients
wp transient delete --expired --path="$WP_PATH"

# Delete spam comments
wp comment delete $(wp comment list --status=spam --field=ID --path="$WP_PATH" 2>/dev/null) --force --path="$WP_PATH" 2>/dev/null

# Delete trashed comments older than 30 days
wp comment delete $(wp comment list --status=trash --field=ID --path="$WP_PATH" 2>/dev/null) --force --path="$WP_PATH" 2>/dev/null

# Delete trashed posts older than 30 days
wp post delete $(wp post list --post_status=trash --field=ID --path="$WP_PATH" 2>/dev/null) --force --path="$WP_PATH" 2>/dev/null

# Optimize tables
wp db optimize --path="$WP_PATH"

Schedule it with cron to run weekly during off-peak hours:

0 3 * * 0 /path/to/wordpress-db-cleanup.sh >> /var/log/wp-db-cleanup.log 2>&1

This runs every Sunday at 3 AM. For details on cron syntax, see Cron job syntax: a practical guide with examples. For running WP-CLI commands via cron specifically, see How to run a WP-CLI command with cron.

Using a plugin for optimization#

If you prefer a GUI approach or do not have SSH access, WP-Optimize is the most established database optimization plugin. It handles revision cleanup, transient deletion, spam removal, and table optimization through the WordPress admin dashboard.

When choosing an optimization plugin, avoid ones that:

• Run optimization on every page load (this adds overhead to every request)
• Store their own logs in the database (ironic but common)
• Claim to “speed up your database” by changing MySQL server settings (they cannot – those settings are server-level)

One optimization at a time is enough. Do not install three database optimization plugins hoping they stack – they do the same thing and the overhead of running three plugins negates any benefit.

How database size affects performance#

A common misconception is that database size directly determines query speed. A 500 MB database is not inherently slow. What matters is how the data is organized, indexed, and queried.

The wp_options autoload problem is the most impactful. If your autoloaded options total 5 MB, WordPress loads 5 MB into PHP memory before it does anything else on every page request. This affects every page, every user, every time.

Large wp_postmeta tables slow down post queries. WordPress uses wp_postmeta for custom fields, and many plugins add dozens of meta entries per post. A WooCommerce store with 10,000 products and 30 meta fields each has 300,000 rows in wp_postmeta . Queries that join wp_posts with wp_postmeta scan these rows. If the meta keys are not indexed (and by default, wp_postmeta only indexes post_id and meta_key separately), complex queries slow down.

Table fragmentation affects read performance on MyISAM tables more than InnoDB. InnoDB manages its buffer pool efficiently enough that moderate fragmentation has minimal impact. If you are running InnoDB (which you should be), fragmentation is a secondary concern behind autoloaded options and orphaned metadata.

Disk I/O matters more than you might think. A large database on slow disk storage (spinning HDD) performs noticeably worse than the same database on NVMe. If your database queries are slow even after optimization, the storage type may be the bottleneck. See NVMe WordPress hosting: why storage type affects your site speed for how storage architecture affects database performance.

Object caching: keeping queries out of MySQL entirely#

Database optimization reduces the cost of queries that do run. Object caching prevents queries from running at all.

WordPress has a built-in object cache that stores query results in PHP memory during a single request. But this cache is not persistent – it is rebuilt from scratch on every page load. A persistent object cache stores results in an external service (Memcached or Redis) so they survive across requests.

With a persistent object cache enabled:

• WordPress options are loaded from Memcached instead of running  SELECT * FROM wp_options WHERE autoload = 'yes'  on every request
• User metadata, rewrite rules, and active plugin lists are served from cache
• Plugins that make repeated database queries benefit automatically
• WooCommerce product data, tax rates, and shipping zones load from cache instead of complex JOIN queries

The impact depends on how database-heavy your site is. A simple blog might see 10-20% fewer database queries. A WooCommerce store with thousands of products might see 60-80% fewer queries because product data and taxonomy lookups are heavily cached.

Memcached vs Redis for WordPress

Both work as persistent object caches. The practical differences for WordPress:

• Memcached is simpler, faster for pure key-value lookups, and uses less memory per key. It does what WordPress object caching needs and nothing more.
• Redis supports data structures (lists, sets, sorted sets) and persistence to disk. It is more versatile but uses more memory and is overkill for most WordPress object caching needs.

For WordPress specifically, Memcached is the more efficient choice unless you have a specific need for Redis data structures (some plugins require Redis).

Database optimization on Hostney#

Hostney’s architecture addresses several database performance issues at the infrastructure level.

Per-account Memcached. Each hosting account gets a dedicated Memcached instance running on a Unix socket – not a shared pool. One customer’s cache evictions cannot clear another customer’s cached data. Enable it through the control panel, and the Hostney Cache plugin automatically installs the WordPress object cache drop-in. No configuration needed – the plugin detects the per-account Memcached socket and connects to it.

Automatic page caching. The Hostney Cache plugin also handles Nginx FastCGI page cache purging. When you publish or update content, it purges exactly the right cached pages – the post URL, the homepage, RSS feeds, sitemaps, and related archive pages. This means cached pages always show fresh content without you manually clearing the cache. For a detailed look at how the purging logic works, see How Hostney handles WordPress cache purging automatically.

Database size monitoring. Instead of letting a database grow silently until it causes MySQL connection errors or fills the server disk, the infrastructure monitors database sizes continuously. If a database exceeds its plan limit, it automatically switches to read-only mode – the site continues serving content while you clean up the data. No crash, no downtime for other sites, no “MySQL server has gone away” because the disk filled up.

NVMe storage. All databases run on NVMe storage, which means even unoptimized queries with table scans run faster than they would on spinning disks. This does not replace proper optimization – a bad query is still a bad query – but it raises the floor for database performance. The difference is most noticeable on WooCommerce stores with complex product queries that join multiple tables.

SSH and WP-CLI access. All the command-line optimization techniques in this guide work directly on Hostney. SSH into your account and run WP-CLI commands to clean up revisions, delete transients, optimize tables, and inspect autoloaded options. No plugin needed for database maintenance if you are comfortable with the command line.

Summary#

WordPress database bloat comes from five main sources: unlimited post revisions, expired transients, spam comments, orphaned metadata, and autoloaded options from removed plugins. The autoloaded options problem has the biggest performance impact because that data loads on every single page request.

Clean up with WP-CLI ( wp transient delete --expired , wp post delete revisions, wp db optimize ), limit future revisions in wp-config.php , and schedule weekly cleanup via cron. For ongoing performance, a persistent object cache (Memcached or Redis) reduces database load far more than any cleanup routine by preventing repeated queries from hitting MySQL at all.

If your database queries are still slow after cleanup and optimization, the bottleneck may be storage I/O rather than the data itself. And if you are seeing intermittent timeout errors during heavy database operations, see MySQL server has gone away: what it means and how to fix it for the connection-level diagnosis.