WordPress Database Optimisation: How to Clean and Speed Up Your Database

What’s Actually in Your WordPress Database

Every WordPress installation uses a MySQL database with a core set of tables. Understanding what each table stores — and what causes it to grow — is essential before you start cleaning.

wp_posts: Every piece of content — pages, posts, custom post types, revisions, attachments (media metadata), menu items, and WooCommerce products/orders. This is usually the largest table by row count, primarily because of revisions. A site with 100 posts and unlimited revisions can easily have 5,000+ rows in wp_posts.

wp_postmeta: Metadata for everything in wp_posts. Custom fields, SEO data (Yoast/Rank Math), page builder layout data, WooCommerce product details (price, stock, SKU), and ACF fields. This table often has 10–50x more rows than wp_posts. On WooCommerce sites, it’s frequently the largest table in the entire database and the primary source of slow queries. For a complete approach to WooCommerce database and performance issues, see our WooCommerce speed optimisation guide.

wp_options: Site-wide settings — WordPress core settings, theme options, plugin settings, widget configurations, and transients. This is the most performance-critical table because WordPress loads all autoloaded options into memory on every single page request, regardless of whether the data is needed.

wp_terms, wp_term_taxonomy, wp_term_relationships: Categories, tags, and custom taxonomies. These tables rarely cause performance issues unless you have thousands of terms or complex hierarchical taxonomies.

wp_comments, wp_commentmeta: Comments and their metadata. Spam comments accumulate here if not cleaned regularly. Large comment tables slow down post queries because WordPress joins against them.

wp_usermeta: User metadata — capabilities, preferences, and plugin-specific user data. On sites with thousands of registered users (membership sites, WooCommerce stores), this table can grow significantly.

What Causes Database Bloat

WordPress databases don’t start bloated. They grow over time through a combination of default behaviours and plugin side effects.

Post revisions (unlimited by default): Every time you save a post or page, WordPress creates a revision — a full copy of the content stored as a separate row in wp_posts with corresponding rows in wp_postmeta. By default, there is no limit on the number of revisions stored. A page edited 200 times has 200 revisions, each with its own metadata. On sites using page builders (Elementor, Divi), revisions are especially destructive because each revision stores the entire serialised page builder layout — often 100KB+ per revision.

Expired transients: Transients are temporary cached data stored in wp_options (or in the object cache if Redis is active). Plugins use transients to cache API responses, remote data fetches, and computed values. When transients expire, WordPress doesn’t automatically delete them from the database — they linger until garbage collection runs, which is unreliable. A site with 20 plugins can accumulate thousands of expired transients, each a row in wp_options.

Autoloaded options from deleted plugins: When you install a plugin, it adds rows to wp_options with autoload = yes. When you delete the plugin, most plugins do not clean up their options. Those rows remain in wp_options, autoloaded on every page request, forever. We routinely find 2–5MB of autoloaded options from plugins that were deleted years ago. WordPress loads all of this into memory on every page load, even though none of it is used.

Orphaned postmeta: When a post is deleted, WordPress deletes the wp_posts row but doesn’t always clean up associated wp_postmeta rows. Over time, wp_postmeta accumulates thousands of orphaned rows pointing to post IDs that no longer exist. These rows waste space and slow down meta queries.

Spam and trashed content: Spam comments in the spam queue, trashed posts, and trashed comments remain in the database until manually emptied. On sites without Akismet or with poor spam filtering, the wp_comments table can have tens of thousands of spam rows.

WooCommerce session data: WooCommerce stores customer session data (cart contents, applied coupons) in wp_options as transients. On high-traffic stores, this generates hundreds or thousands of session transient rows that cycle constantly.

How to Check Your Database Size and Slow Queries

Before cleaning, measure. You need to know how large each table is, how much data is autoloaded, and which queries are slow.

Database size by table (WP-CLI):

wp db size --tables --format=table

This shows each table’s size. Look for wp_postmeta and wp_options as the usual suspects. If wp_postmeta is over 100MB, you likely have revision or orphan issues. If wp_options is over 5MB, autoloaded data is probably excessive.

Autoloaded options size (WP-CLI):

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

A healthy site has under 1MB of autoloaded data. We commonly find sites with 5–15MB. One extreme case had 47MB of autoloaded options — WordPress was loading 47MB of data into PHP memory on every single page request.

Largest autoloaded options:

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

This identifies which options are consuming the most memory. Common offenders: serialised theme options, page builder global styles, deleted plugin settings, and cached API responses stored as autoloaded options instead of transients.

Query Monitor plugin: Install Query Monitor on your staging site. It shows every database query on each page load — total count, execution time, duplicate queries, and slow queries (over 0.05 seconds). This is the most valuable WordPress debugging tool available and it’s free. Look for: total query count (should be under 50 for a well-optimised page), total query time (should be under 50ms with object caching), and any individual queries over 10ms.

Cleaning Post Revisions

Post revisions are usually the single largest source of unnecessary database rows. Here’s how to clean them and prevent future bloat.

Count revisions:

wp post list --post_type=revision --format=count

If this number is over 500, you have significant revision bloat. Sites with 5,000+ revisions are common, especially with page builders.

Delete all revisions:

wp post delete $(wp post list --post_type=revision --format=ids) --force

This permanently deletes all revisions. On a site with 5,000 revisions, this can reduce the wp_posts table by 90% and wp_postmeta by even more (because each revision has multiple meta rows).

Limit future revisions: Add this to wp-config.php:

define('WP_POST_REVISIONS', 3);

This limits WordPress to keeping the 3 most recent revisions per post. Three is sufficient for “undo” purposes while preventing unbounded growth. Some people set this to false (disabling revisions entirely), but keeping 2–3 provides a safety net without significant database cost.

Keep recent revisions, delete old ones: If you want to keep recent revisions (last 30 days) but clean older ones, use WP-CLI with a date filter or the WP-Sweep plugin, which allows selective revision cleanup.

Cleaning Transients

Transients are temporary data stored in the database. Cleaning them is safe — they’re cached data that will be regenerated when needed.

Delete all transients:

wp transient delete --all

This removes all transients (both expired and current) from wp_options. Current transients will be regenerated automatically when the code that created them runs again. This is completely safe and a good maintenance practice.

The Redis solution: When Redis object caching is active, transients are stored in Redis (memory) instead of wp_options (database). This eliminates transient bloat from the database entirely and makes transient access dramatically faster. This is one of several reasons Redis is essential — it’s not just about query caching, it’s about keeping your database clean.

Fixing Autoloaded Options — The Hidden Killer

Autoloaded options are the most impactful and least understood source of WordPress database performance problems.

The problem explained: On every page request, before WordPress does anything else, it executes: SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'. Every row returned by this query is loaded into PHP memory. If you have 5MB of autoloaded data, WordPress allocates 5MB of PHP memory before it even starts building your page. This adds 100–500ms to every uncached page load.

Identify bloated autoloaded options:

wp db query "SELECT option_name, LENGTH(option_value) as size_bytes FROM wp_options WHERE autoload = 'yes' ORDER BY size_bytes DESC LIMIT 30"

Look for: options from plugins you’ve deleted (the plugin is gone but its data remains), large serialised arrays (theme options, page builder settings), cached data that should be transients instead of autoloaded options, and any individual option over 100KB.

Fix 1 — Delete options from removed plugins: If you see options with names matching plugins you no longer have installed, it’s safe to delete them:

wp option delete some_old_plugin_settings

Fix 2 — Change unnecessary options to non-autoloaded: Some options are legitimately needed but don’t need to be autoloaded (loaded on every page). Plugin settings that are only used on specific admin pages, for example. You can change the autoload flag:

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

Be cautious: only change options to non-autoloaded if you understand what they do. Core WordPress options and active theme/plugin settings should remain autoloaded. The targets are options from deleted plugins and options from active plugins that are only used in the admin dashboard.

Fix 3 — Clean oversized options: Some plugins store enormous amounts of data in a single autoloaded option — logging plugins, analytics plugins, and poorly-coded themes are common offenders. If an option is over 500KB, investigate whether the plugin offers a setting to limit the stored data, or whether the option can safely be deleted and regenerated.

Database Table Optimisation

After deleting revisions, transients, and orphaned data, your tables may contain fragmented free space. MySQL doesn’t automatically reclaim this space — you need to optimise the tables explicitly.

wp db optimize

This runs OPTIMIZE TABLE on every WordPress table. It reclaims free space, defragments the data, and updates index statistics. On a database that’s just been cleaned of thousands of revisions, this can reduce the physical database file size by 30–60%.

When to run: After any major cleanup operation. As monthly maintenance. Not during peak traffic hours — the operation locks tables briefly, which can cause slowdowns on high-traffic sites.

Adding indexes for slow queries: If Query Monitor shows specific slow queries (over 10ms), adding a database index can dramatically improve their performance. Common cases:

-- Speed up postmeta lookups (especially for WooCommerce)
ALTER TABLE wp_postmeta ADD INDEX meta_value_index (meta_value(191));

Only add indexes for queries you’ve confirmed are slow via Query Monitor. Unnecessary indexes waste memory and slow down writes (inserts and updates).

Object Caching with Redis

We covered Redis briefly in our caching guide, but its impact on database performance deserves deeper coverage here.

Without Redis: Every page load queries the database 30–100 times. Many of these queries are identical across page loads — site options, menu structures, widget configurations, plugin settings. Each query takes 0.5–5ms depending on table size and server load. Total query time: 50–200ms per page. Our server-level optimisation guide covers how to install and configure Redis without any plugins.

With Redis: The first page load executes the database queries and stores results in Redis. Subsequent page loads read from Redis instead of MySQL. Redis operations take 0.02–0.1ms compared to 0.5–5ms for MySQL queries. Total query time drops to 5–20ms per page — a 10x improvement.

Setup on Cloudways:

1. Enable Redis from your application’s “Packages” tab in the Cloudways panel
2. Install the Redis Object Cache plugin from the WordPress plugin directory
3. Go to Settings → Redis in WordPress admin and click “Enable Object Cache”
4. Verify: the plugin’s status page should show “Connected” and display hit/miss ratios

Monitoring Redis effectiveness: The Redis Object Cache plugin shows a hit ratio — the percentage of data lookups served from cache vs querying the database. A healthy site should see a hit ratio above 85%. Below 70% suggests your cache is being cleared too frequently or your cache size is too small.

WooCommerce-Specific Database Issues

WooCommerce sites face unique database challenges due to the volume of transactional data and the custom database structures WooCommerce uses.

The wp_postmeta problem: WooCommerce traditionally stores all product data (price, stock, SKU, weight, dimensions, gallery images, attributes) in wp_postmeta. For a store with 1,000 products, each with 30+ meta fields, wp_postmeta easily exceeds 30,000 rows. Complex product queries (filtering by price, stock status, attributes) require joining wp_posts to wp_postmeta multiple times — and wp_postmeta’s key-value structure is inherently slow for these operations.

HPOS (High-Performance Order Storage): WooCommerce 8.2+ introduced HPOS — a dedicated set of tables for order data (wp_wc_orders, wp_wc_orders_meta, etc.) instead of storing orders in wp_posts/wp_postmeta. This dramatically improves order query performance and reduces wp_postmeta size. If you’re on WooCommerce 8.2+, enable HPOS in WooCommerce → Settings → Advanced → Features. Existing orders will need to be migrated — WooCommerce provides a built-in migration tool.

Session data cleanup: WooCommerce stores customer sessions as transients in wp_options. On high-traffic stores, this generates hundreds of session rows daily. With Redis enabled, sessions are stored in memory instead of the database. Without Redis, schedule regular cleanup of expired WooCommerce sessions using WP-CLI or the WooCommerce built-in session cleanup (runs via wp-cron, but wp-cron reliability is a separate issue — see our main guide).

Ongoing Maintenance Schedule

Database optimisation isn’t a one-time task. Without regular maintenance, the bloat returns. Here’s the maintenance schedule we recommend for every WordPress site:

Weekly: Delete spam comments and empty trash (wp comment delete $(wp comment list --status=spam --format=ids) --force).

Monthly: Delete expired transients (wp transient delete --expired). Run wp db optimize. Check autoloaded options size — if it’s grown by more than 10%, investigate.

Quarterly: Full revision audit — delete old revisions keeping the last 3 per post. Check for orphaned postmeta. Review autoloaded options for deleted plugin remnants. Run Query Monitor on key pages to check for slow queries.

After every plugin removal: Check wp_options for leftover data from the removed plugin. Delete any options that were created by that plugin.

Automate what you can. Set WP_POST_REVISIONS to 3 to prevent revision accumulation. Use Redis to prevent transient database bloat. Schedule WP-CLI cleanup commands via real cron (not wp-cron). The goal is a database that stays clean by default, with periodic verification rather than reactive cleanup.

Frequently Asked Questions