WordPress Database Optimization

Measure the queries before you delete anything. Most "optimise database" advice targets table overhead and revisions, which are rarely what makes a WordPress database slow. The real costs are data read on every request, data written on every request, and queries that scan far more rows than they return.

Take a backup first — how to back up a WordPress site — because every fix below deletes or changes data.

Measure first

Install Query Monitor on a staging copy or briefly on production, load a slow page logged in, and open the Queries panel. Three numbers matter:

• Total query time for the page. Under 50 ms is healthy; several hundred means the database is a real part of the problem.
• Slow queries, which it highlights, with the component that ran them.
• Duplicate queries, a sign of a plugin looping without caching its own results.

If query time is small but the page is still slow, the database is not your bottleneck — the wider picture is in reducing server response time. An admin that is slow while the front end is fast has its own causes, covered in speeding up the WordPress admin.

The culprits, in order of impact

1. Autoloaded options

WordPress loads every option flagged for autoload into memory on every request, before it knows what the page needs. Plugins that store large arrays there — and plugins that were deleted without cleaning up — make every request pay for them.

Since WordPress 6.6 the autoload column holds on, off, auto, auto-on and auto-off alongside the legacy yes/no; four of them mean "autoload", so query all four:

SELECT option_name, LENGTH(option_value) AS bytes
FROM wp_options
WHERE autoload IN ('yes', 'on', 'auto-on', 'auto')
ORDER BY bytes DESC
LIMIT 20;

Identify each large row by its prefix. A row belonging to a plugin that is no longer installed can usually be deleted; one belonging to an active plugin can be switched to not autoload instead:

wp option set-autoload some_plugin_cache no

2. Transients that never go away

Without a persistent object cache, transients are stored as rows in the options table. Most expire, but expired ones are only removed when something asks for them or the daily cleanup runs — and that depends on cron firing, which is unreliable on low-traffic sites, as disabling WP-Cron explains.

wp transient delete --expired
wp db query "SELECT COUNT(*) FROM wp_options WHERE option_name LIKE '\_transient\_%'"

If the count climbs straight back up, a plugin is writing a transient per visitor or per URL. Deleting them is a bandage; that plugin is the fix.

3. Postmeta bloat

wp_postmeta holds one row per field per post, and its meta_value column is not indexed. Queries that filter on meta values — product filters, custom field searches, some page builders — scan large parts of the table. Uninstalled plugins leave their keys behind:

SELECT meta_key, COUNT(*) AS rows_count
FROM wp_postmeta
GROUP BY meta_key
ORDER BY rows_count DESC
LIMIT 20;

Delete only keys whose prefix you can match to a plugin that is gone, and check on staging first. The plugin side of this job is in choosing a database management plugin.

4. WooCommerce tables

Stores add their own weight. Action Scheduler keeps completed and canceled actions, with their logs, for 30 days by default (action_scheduler_retention_period); failed actions are never purged automatically, and a busy store can hold millions of rows. Old sessions accumulate in wp_woocommerce_sessions. Switching to High-Performance Order Storage, then turning off compatibility sync and removing the legacy copies (wp wc hpos cleanup all), takes orders out of wp_posts and wp_postmeta, which speeds up both orders and everything else sharing those tables. The full store picture is in speeding up WooCommerce.

5. No object cache

Without a persistent object cache, WordPress repeats the same queries on every request because its in-memory cache dies with the request. Redis or Memcached keeps those results between requests. It is the single biggest database win for logged-in users, stores and membership sites — the traffic page caching cannot serve. It needs host support; where it fits in the stack is in the WordPress server stack.

6. Revisions

Revisions grow wp_posts and slow the editor, but rarely the front end. Cap them rather than deleting history in bulk — WordPress post revisions covers both.

Scheduled cleanup

Revisions, auto-drafts, trashed posts, spam comments and expired transients come back, so a recurring cleanup is more useful than a one-off. If you already run WP Rocket for caching, its Database tab covers this on a daily, weekly or monthly schedule — skip its table-optimisation option for the reason below. Be clear about the limit: it does not touch autoloaded options, orphaned meta or indexes, which is where the larger wins are.

MySQL tuning, only on a VPS

On shared or managed hosting you cannot change server settings, and you should not need to. On your own server, the setting that matters most is innodb_buffer_pool_size: if the working set of your tables fits in memory, reads stop hitting disk — size it to your data plus indexes, up to about 50–70% of RAM on a server that only runs the database. Leave the other tuning knobs alone unless you are measuring the effect of each.

OPTIMIZE TABLE is mostly cosmetic on InnoDB. It reclaims disk space after a large deletion; it rarely makes queries measurably faster, and on a large table the rebuild is heavy on disk I/O and needs free space roughly equal to the table's size while it runs.

Common mistakes

• Running "optimise everything" on production without a backup or a look at what it deletes.
• Deleting autoloaded options by guessing at the owner — an active plugin's settings vanish with them.
• Treating table overhead as the problem because a dashboard shows it in red.
• Clearing transients every hour while the plugin generating them keeps running.
• Skipping the object cache on a store and trying to fix logged-in speed with page caching.

Verify

Reload the same page with Query Monitor and compare total query time against the first measurement. Re-run the autoload query and confirm the total is under about 800 KB. Then load the site logged out and check that nothing changed except speed: forms submit, filters return results, and checkout completes.