Database optimization for WordPress: a step-by-step guide

Introduction

WordPress databases grow quietly over time, accumulating revisions, orphaned metadata, and expired transients that slow down queries and consume unnecessary disk space. A well-maintained database can mean the difference between a site that responds in milliseconds and one that struggles under load—and database optimization for WordPress: a step-by-step guide gives us the concrete toolkit to keep our installations lean and performant.

We've all inherited WordPress sites where the database had bloated to 500 MB or more, or watched query times spike during traffic surges. The fix often isn't about buying more hardware; it's about removing the cruft and tuning the queries that actually matter. In this article, we'll walk through practical optimization strategies that senior engineers use in production to maintain database health at scale.

Clean up revisions and transients

By default, WordPress keeps every revision of every post and page. If a post has been edited 50 times, you have 51 rows in the wp_posts table (the original plus 50 revisions). Over months or years, this adds up fast—especially on content-heavy sites or those using post types for recurring data entry.

The same issue affects transients. These are time-limited cached options stored in the database. When transients expire, WordPress doesn't automatically clean them up; they just sit there consuming space. A site with heavy plugin usage can accumulate thousands of expired transients.

Here's a practical cleanup routine we run during off-peak maintenance windows:

// Remove all post revisions
DELETE FROM wp_posts WHERE post_type = 'revision';

// Clean the postmeta table of orphaned revision data
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON pm.post_id = wp.ID
WHERE wp.ID IS NULL;

// Remove expired transients
DELETE FROM wp_options 
WHERE option_name LIKE '%_transient_%' 
AND option_value = '';

// More aggressive: remove all transients (use carefully in production)
DELETE FROM wp_options 
WHERE option_name LIKE '_transient_%' 
OR option_name LIKE '_transient_timeout_%';

To prevent revisions from piling up, we also set limits in wp-config.php:

// Keep only the last 5 revisions per post
define( 'WP_POST_REVISIONS', 5 );

// Or disable revisions entirely for specific post types
define( 'WP_POST_REVISIONS', false );

For a more surgical approach, consider using a management plugin with built-in scheduling, but understand what it's doing behind the scenes. We've found that running these queries directly on a staging environment first, measuring the before and after, gives us confidence before touching production.

Limit autoloaded options

WordPress loads all options with autoload = yes into memory on every request. This happens early in the bootstrap process, before your code even runs. If you have 200 autoloaded options with serialized data, you're loading potentially megabytes of unnecessary data every single time a page is requested.

We've debugged situations where a single plugin stored entire configuration arrays as autoloaded options, causing 5–10 MB of data to be loaded and unserialize on every request. The impact scales with traffic.

First, audit what's autoloading. Run this query to see the damage:

SELECT option_name, option_value, LENGTH(option_value) as size_bytes
FROM wp_options
WHERE autoload = 'yes'
ORDER BY LENGTH(option_value) DESC
LIMIT 20;

Then fix the worst offenders. If an option is only needed in the admin or during specific actions, disable autoload:

// In plugin code, when updating an option:
update_option( 'my_plugin_large_config', $config_array, false );
// The fourth parameter is autoload. False = don't autoload.

For existing problematic options:

// Disable autoload for large serialized options
UPDATE wp_options 
SET autoload = 'no' 
WHERE option_name IN ('your_plugin_settings', 'another_large_option');

On one e-commerce site, disabling autoload on six problematic options reduced the options data loaded per request from 8 MB to 400 KB. Page load time improved by 15–20 percent before any other optimization.

Add indexes for custom queries

WordPress core tables have indexes for common queries, but custom post meta queries or complex custom table structures often lack them. Without proper indexing, MySQL performs full table scans, which become glacially slow as data grows.

If you're running custom queries against large tables—particularly meta tables—you need indexes. Here's a real scenario: a SaaS plugin stored customer usage data in wp_postmeta, querying by meta key and value. At 100,000 records, queries that once took 10 ms now took 3 seconds because no index existed on the meta_key and meta_value columns.

Check which queries are running on your site and identify slow ones. For a custom query like:

$results = get_posts( array(
    'post_type' => 'order',
    'meta_query' => array(
        array(
            'key'   => 'customer_id',
            'value' => 42,
        ),
    ),
) );

You need an index on postmeta's meta_key and post_id at minimum. WordPress adds these by default, but if you're using a custom table, add indexes explicitly:

// In plugin activation or migration code
global $wpdb;

$charset_collate = $wpdb->get_charset_collate();

$sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}custom_data (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    meta_key VARCHAR(255) NOT NULL,
    meta_value LONGTEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_meta_key (meta_key),
    INDEX idx_user_meta (user_id, meta_key)
) $charset_collate;";

require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );

The multi-column index idx_user_meta is crucial for queries filtering by both columns. This alone can reduce query time from seconds to milliseconds.

Use Query Monitor to find slow queries

You can't optimize what you don't measure. Query Monitor is a free WordPress plugin that shows every database query executed during a page load, how long each took, and where in your code it came from. It's indispensable for identifying optimization targets.

Install Query Monitor, load a page, and open the toolbar. You'll see:

• Total query count (a sign of inefficiency if it's over 50–100 on a simple page)
• Slowest queries listed with timing and the file/line that triggered them
• Duplicate queries (often a sign of inefficient loops)
• Query output, warnings, and deprecated usage

On one site, Query Monitor revealed that a custom plugin was running the same meta query 47 times per page load inside a loop. Adding a single wp_cache_add() call reduced that to one query. The improvement was dramatic.

Once you've identified slow queries with Query Monitor, use the EXPLAIN statement to understand why they're slow:

EXPLAIN SELECT * FROM wp_posts p
INNER JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE p.post_type = 'order' 
AND pm.meta_key = 'order_status'
AND pm.meta_value = 'completed';

If the type column shows ALL, you have a full table scan and need an index. If it shows range or ref, the index is being used efficiently.

Common mistakes

Disabling autoload too aggressively. Some teams disable autoload on everything, then end up with 10+ separate queries later to fetch those options on demand. Autoload is fine for small, frequently-used options. It's only a problem when options are large or rarely used.

Adding indexes without measuring impact. Adding an index to a small table or low-traffic query can hurt write performance without helping reads. Always EXPLAIN your queries and measure before indexing.

Cleaning the database without staging first. Running aggressive cleanup queries on production without testing them on a copy first has caused downtime. Always test locally or in staging, measure the impact, and schedule the change during low-traffic windows.

Ignoring plugin performance. Many performance problems come from poorly-written plugins that don't cache, that run queries in loops, or that use inefficient post type queries. Audit your plugins with Query Monitor before assuming WordPress core is the issue.

Setting WP_POST_REVISIONS to 0 (zero) vs. false. Zero is not the same as false. Use define( 'WP_POST_REVISIONS', false ); to disable revisions, not 0.

Key takeaways

• Revisions and expired transients are low-hanging fruit. Clean them up regularly and set limits in wp-config.php.
• Audit autoloaded options and disable autoload for large, infrequently-accessed data. This can improve page load time significantly.
• Add indexes to custom queries before they become a bottleneck. Use EXPLAIN to verify the index is being used.
• Use Query Monitor in development and staging to identify slow queries and N+1 problems before they hit production.
• Always test database changes on a copy first. Measure before and after. Document what you changed and why.