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

Apr 11, 2026|14 min read

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. Database cleanup is one layer of a broader WordPress speed optimization stack that includes caching, image optimization, and hosting. 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.

Diagnosing what to optimize first#

Before cleaning anything, run these diagnostic queries to find where the biggest gains are. This tells you whether your problem is autoload bloat, revision buildup, or orphaned data – so you fix the right thing first.

Check overall database size by table:

wp db query "SELECT TABLE_NAME, ROUND(DATA_LENGTH/1024/1024, 2) AS data_mb, ROUND(INDEX_LENGTH/1024/1024, 2) AS index_mb, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() ORDER BY DATA_LENGTH DESC LIMIT 15;"

Expected output on a typical WooCommerce store:

wp_postmeta          85.42    12.30    482910
wp_posts             34.21     3.18     28450
wp_options           22.87     0.95      3841
wp_wc_order_stats     8.33     1.22     15200
wp_comments           4.12     0.88     12300

If wp_postmeta dominates (common on WooCommerce sites), orphaned metadata cleanup will have the biggest impact. If wp_options is unexpectedly large, autoload bloat is the problem.

Count revisions, transients, and spam in one pass:

wp db query "SELECT 'revisions' AS type, COUNT(*) AS total FROM wp_posts WHERE post_type = 'revision' UNION ALL SELECT 'expired_transients', COUNT(*) FROM wp_options WHERE option_name LIKE '_transient_timeout_%' AND option_value < UNIX_TIMESTAMP() UNION ALL SELECT 'spam_comments', COUNT(*) FROM wp_comments WHERE comment_approved = 'spam' UNION ALL SELECT 'trashed_comments', COUNT(*) FROM wp_comments WHERE comment_approved = 'trash' UNION ALL SELECT 'orphaned_postmeta', COUNT(*) FROM wp_postmeta pm LEFT JOIN wp_posts p ON pm.post_id = p.ID WHERE p.ID IS NULL;"

This gives you exact numbers so you know whether cleaning revisions saves 200 rows or 200,000. Focus effort where the numbers are largest.

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_<em> and _site_transient_</em> 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_<em> , wp_yoast_</em> ), 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.

An unmonitored database can grow silently until it causes MySQL connection errors or fills the server disk. Some managed hosts handle this for you – on Hostney, databases that exceed their plan limit automatically switch to read-only mode so the site continues serving content while you clean up, rather than crashing or taking down other sites on the server.

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. All of these WP-CLI commands work over SSH on any host that provides shell access – on Hostney, SSH and WP-CLI are available on all plans, so you can run database maintenance from the command line without needing a plugin.

Using a plugin for optimization#

If you prefer a GUI approach or do not have SSH access, several plugins handle database cleanup through the WordPress admin.

WP-Optimize is the most established option. It handles revision cleanup, transient deletion, spam removal, table optimization, and scheduled cleanup through a single interface. It also shows you how much space each cleanup operation will reclaim before you run it.

Advanced Database Cleaner takes a more targeted approach. It scans for orphaned tables left by uninstalled plugins and lets you review each one before deleting. It also detects orphaned metadata and autoloaded options with unusually large values. This is the better choice if you want to clean up after removed plugins specifically.

WP-Sweep is the lightweight option. It focuses on cleaning revisions, drafts, trashed posts, spam comments, orphaned metadata, and transients with no extra features. If you want cleanup without the bloat of a full optimization suite, this is it.

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.

To see the actual impact of optimization, measure before and after. Check your current query load with:

wp db query "SHOW GLOBAL STATUS LIKE 'Slow_queries';"
wp db query "SHOW GLOBAL STATUS LIKE 'Questions';"

For WordPress-specific timing, enable the query log temporarily by adding define('SAVEQUERIES', true); to wp-config.php , then check $wpdb->queries to see how many queries run per page load and how long each takes. A typical unoptimized WooCommerce store runs 200-400 queries per page load. After cleanup and object caching, that drops to 30-80.

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. Hostney runs all databases on NVMe storage, which means even unoptimized queries with table scans run faster than they would on spinning disks – but that does not replace proper optimization. A bad query is still a bad query regardless of storage speed. 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). On Hostney, each hosting account gets a dedicated Memcached instance running on a Unix socket – not a shared pool, so one customer’s cache evictions cannot clear another customer’s cached data. Enable it through the control panel and the Hostney Cache plugin handles the rest automatically – it detects the per-account Memcached socket and installs the WordPress object cache drop-in. The same plugin also handles Nginx FastCGI page cache purging: when you publish or update content, it purges exactly the right cached pages (the post URL, homepage, RSS feeds, sitemaps, and related archives) so cached pages always show fresh content without manual intervention.

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.

WordPress 500 internal server error: what it means and how to fix it

How to Fix the Most Common WordPress Errors