WordPress Database Optimization: The Complete Method

by Francis Rozange | Oct 2, 2026 | Performance

Stacked glass drawers, some overflowing, one being put back in order

In the spring of 2025, a WooCommerce performance optimization filled some stores’ databases with millions of pending jobs. On one of them, the queue held seven million pending jobs, all of the same kind. The fix was merged in under four hours and released the next day, but the bloated tables stayed.

A WordPress database rarely grows all at once. It gets heavier with options loaded on every visit, forgotten transients, accumulated revisions, orphaned metadata and, on a store, sessions and scheduled jobs. One day, the admin area slows down, Site Health raises a warning, or the host flags that you have exceeded your disk space.

This guide offers a method, in order: back up and measure, then deal with each source of weight, autoloaded options, transients, revisions, metadata, WooCommerce, and finally storage itself, with the right tools and a maintenance routine.

Before touching the database: back up and measure

Export, then check the backup restores

Any cleanup starts with a full export of the database, ideally tested on a copy of the site. With WP-CLI, one command is enough, and the backup should be stored outside the site’s public folder.

wp db export ~/before-cleanup.sql

A backup is only worth something if it restores. Our comparison of WordPress backup plugins details the tools that do it without the command line.

Work on a copy

The heaviest cleanups, mass revision deletion, table conversion, rebuilds, are tested first on a copy of the site. There you measure the real gain, the time needed and the disk space consumed, then apply the same sequence in production, at a quiet hour. On a store, pick a moment with no orders in progress.

Measure before acting

Site Health shows the database size in its Info tab, under Directories and Sizes. WP-CLI gives the detail table by table, which immediately points to the culprits.

wp db size --tables --human-readable

Write these figures down. They will help you check that each cleanup had the expected effect, and spot a table that grows back.

Autoloaded options: the first suspect

What each request loads

The options table holds the site’s and plugins’ settings. Some of them are loaded automatically, as one block, on every page view, whether they are used or not. A plugin that stores a large setting there, or an uninstalled plugin that left its data behind, therefore weighs down every visit.

That block also goes through the object cache when there is one. At WordPress VIP, which uses Memcached, an object cannot exceed one megabyte: an oversized options block triggers an error there. Our guide to Redis object caching explains the mechanism.

What WordPress 6.6 and 6.7 changed

Core itself was long part of the problem. In 2023, a fix in WordPress 6.4 had to deal with an internal core transient, with no expiration and therefore autoloaded, which reached about 2 MB on a site with thirty plugins.

WordPress 6.6, in July 2024, changed the rule: an option added without an explicit instruction is no longer autoloaded if it exceeds 150,000 bytes. The autoload column accepts new values, on, off, auto, auto-on and auto-off, and WordPress 6.7 deprecated the old yes and no values.

One trap remains: this rule does not clean up existing options. An option already stored as yes or on keeps autoloading, even if it grows past the limit.

The Site Health check

Since WordPress 6.6, Site Health reports a critical issue when the total of autoloaded options exceeds 800,000 bytes. That threshold is an alert, adjustable through a filter, not a technical limit.

Audit with the right query

Many recent guides still use a query that only counts the yes value. Since 6.6, it underestimates the total. The WP-CLI command wp option list --autoload=on has the same flaw: it ignores the auto and auto-on values, which are autoloaded. Here is a correct query, to adapt to your table prefix.

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;

Faced with a large option, turn off its autoloading rather than deleting it: it is reversible, and it is enough to lighten every request. WP-CLI does it without touching the value.

wp option set-autoload option_name off

Data left by plugins

Uninstalling a plugin does not always remove its options: many leave them behind, sometimes autoloaded. Felix Arntz, a core contributor, sums up good practice for developers: an option used only in special circumstances should not be autoloaded. To spot leftovers, sort options by prefix: most plugins name their settings with their own prefix, which lets you tie them to an installed or departed plugin.

The Performance Lab plugin, from the WordPress performance team, adds a table of the heaviest options to Site Health, with a button to turn off their autoloading. AAA Option Optimizer watches which options are actually used while browsing, with one limit: its author recommends about a week of tracking, so an option used once a month by a scheduled task will then look useless.

Transients: a cache that settles in

Expiration and autoloading

Transients are temporary data that plugins store with a lifetime. One detail in the code matters a lot: a transient created with an expiration is not autoloaded, but a transient created without one is, and never expires. Those are the ones that weigh down the options block.

An expired transient is deleted when it is read, and a daily task deletes the others. But that task is only scheduled after a visit to the admin area, and does nothing when an external object cache is active: transients then live in the cache, no longer in the database.

Purging without breaking things

WP-CLI distinguishes two operations. The first deletes expired transients, with no risk. The second deletes all transients, which forces plugins to recompute their data, with a temporary slowdown.

wp transient delete --expired
wp transient delete --all

The documentation is clear: a transient’s lifetime is a maximum, not a minimum. A well-written plugin should never assume a transient is still there.

A store’s transients

WooCommerce stores many computations in transients: related products, prices, counters. Its system status tools offer to delete expired transients across the whole site, or to clear its own store transients, which are then rebuilt. The second tool is meant for after a mass product import or a change in pricing rules, not for routine maintenance.

Revisions, auto-drafts and trash

Limiting revisions

By default, WordPress keeps an unlimited number of revisions for each piece of content. On an active editorial site, they end up weighing heavily in the posts and postmeta tables. A constant in wp-config.php sets a ceiling.

define( 'WP_POST_REVISIONS', 3 );

Beware, this ceiling deletes nothing immediately. A post’s extra revisions are only pruned the next time that post is saved. The trash empties automatically after thirty days by default.

Autosaves and drafts

The editor saves the content in progress automatically every sixty seconds by default, an interval you can change with the AUTOSAVE_INTERVAL constant. Each user has only one autosave per piece of content, overwritten each time, and a daily WordPress task deletes auto-drafts, created when you open a new piece of content, once they are seven days old. The main cleanup plugins handle them along with revisions, and the trash is set with EMPTY_TRASH_DAYS.

Delete through the API rather than SQL

To delete all existing revisions, including the most recent ones, go through WP-CLI, which uses WordPress functions and also removes the associated metadata and caches.

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

A raw SQL deletion leaves orphaned metadata behind. Our selection of essential WP-CLI commands details these commands.

Orphaned metadata

Counting them

Orphaned metadata is attached to content that no longer exists. A join is enough to count it, the same one the WP-Optimize plugin uses.

SELECT COUNT(*) FROM wp_postmeta pm
LEFT JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL;

False orphans

Not all data that looks orphaned is. WP-Sweep acknowledges it in its own documentation: Polylang and WPML store data that it mistakes for orphans. On a multilingual site, protect their keys before any cleanup. On a store moved to high-performance order storage, legacy order metadata is cleaned with WooCommerce’s wp wc hpos cleanup all command, once compatibility mode is turned off, not with a generic tool.

WooCommerce: sessions and the job queue

The sessions table

WooCommerce keeps visitors’ carts in a sessions table: two days for a guest, seven days for a logged-in customer by default. A scheduled task cleans it every twelve hours, through Action Scheduler since version 10.1 and in small batches since 10.3. A session length over thirty days is accepted, but WooCommerce logs it as a performance risk.

The tool that clears customer sessions, in the system status tools, empties the whole table: every cart in progress, including logged-in customers’ carts, disappears. It is never a routine maintenance step.

Automatic cleanup since WooCommerce 10.1

WooCommerce 10.1, in August 2025, moved its cleanup tasks, which used to run through WordPress scheduled tasks, to Action Scheduler: expired sessions, unpaid orders, logs. These cleanups therefore depend on the job queue working properly. On a store moved to high-performance order storage, orders live in dedicated tables, as our WooCommerce HPOS guide explains.

The Action Scheduler queue

WooCommerce and many plugins hand their background jobs to Action Scheduler, a library that stores each job in its own tables. Completed or canceled jobs are purged after thirty-one days. Before version 4.0, shipped with WooCommerce 11.0 in August 2026, failed jobs were never purged: they now are, after three months.

Before cleaning, look at what the queue contains, job by job and status by status.

SELECT hook, status, COUNT(*) AS n
FROM wp_actionscheduler_actions
GROUP BY hook, status ORDER BY n DESC LIMIT 20;

An endless conveyor of glass tokens piling up faster than they are cleared

The real case: WooCommerce 9.8 and the seven million jobs

The WooCommerce 9.8 incident, in the spring of 2025, is documented hour by hour on GitHub and on the WordPress.org forums. It shows how a job queue, which is a table like any other, can inflate a database faster than any cleanup.

A performance optimization

In February 2025, a WooCommerce developer proposed computing related products only once a day, and moving the deletion of their transients to a background job, so saving a product would no longer be blocked. The change shipped with WooCommerce 9.8.0, in April 2025.

The flaw fits in one line: every product save or stock change scheduled a new job, without checking whether an identical job was already waiting. On an active catalog, the Action Scheduler jobs table exploded.

April 29, 2025

That morning, a team running several stores opened an issue: the queue table had reached millions of rows, including seven million pending jobs for this single transient deletion on some stores. Rolling back to version 9.7.1 made the problem disappear, and the issue linked to seven forum threads describing the same symptom.

The WooCommerce team replied in a little over an hour and identified the change at fault. Shortly after, a developer acknowledged the flaw: nothing prevented duplicate jobs from being scheduled. The fix adds a memory of products already scheduled in the request and a lookup for an identical job before creating a new one. It was merged the same day, and WooCommerce 9.8.3 shipped on April 30.

The fix does not empty the tables

The following weeks showed the limit of a fix: it stops new jobs from being produced, but does not clean up existing ones. In May, a South African store with forty thousand products, already on 9.8.4, still reported fifteen thousand overdue jobs and very high server load, then rolled back to version 9.7.1 because the situation was hurting it financially.

The sample the team examined showed jobs scheduled on April 10, before the fix, and already completed; the owner then found other MySQL problems, and the team judged the link with the queue unproven.

As early as April 28, the day before the issue was opened, the owner of a small store, barely 177 products but 759 variations, described on the WordPress.org forum a high CPU load he attributed to the same jobs. He disabled the call at fault in the code and made other changes on his site; two days later, his CPU load had dropped. Even a small catalog could be hit, as long as its stock changed often.

On May 23, 2025, on WooCommerce’s English-language support forum, a store hosted by OVH, already running version 9.8.4, reported its database growing from 190 MB to 1 GB in three days, because of the queue tables and their logs. Its owner had been emptying the tables by hand in phpMyAdmin, then scheduled a daily cleanup. Support pointed to the fix and suspected a plugin conflict, without a definitive conclusion.

The long-term answer

This kind of problem, queue tables growing without limit, fed into Action Scheduler 4.0, in June 2026: automatic purging of failed jobs after three months and a dedicated daily cleanup. It shipped with WooCommerce 11.0, on August 4, 2026.

What to take away

A job queue is a table, and an unbounded producer fills it faster than any cleanup. This bloat escaped common tools: the free version of WP-Optimize does not clean the Action Scheduler tables. Diagnose by job and by status before deleting anything.

Fix the source first, by updating, then clean: cleaning first means watching the table fill up again. Clean with the dedicated command, which also deletes the logs, rather than in phpMyAdmin, which leaves orphaned rows. And never delete pending jobs blindly: they include subscription renewals, emails, webhooks.

wp action-scheduler clean --status=complete,failed,canceled --before='31 days ago'

InnoDB, OPTIMIZE TABLE and indexes

Converting leftover MyISAM tables

WordPress does not set a storage engine: its tables take the server’s default, InnoDB on recent versions of MySQL and MariaDB. Very old sites sometimes keep MyISAM tables, which a query on the database schema can spot. Converting to InnoDB needs disk space for both the old and the new table, and a backup first.

This query lists the tables still using MyISAM, and the next command converts one, after a backup.

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE() AND ENGINE = 'MyISAM';

ALTER TABLE wp_example ENGINE=InnoDB;

InnoDB handles concurrent writes and crash recovery better, two qualities that matter on a store or a busy site.

What OPTIMIZE really does

On InnoDB, OPTIMIZE TABLE does not defragment in the usual sense: it rebuilds the table. Deleting rows does not shrink the file; the rebuild returns space to the system when each table has its own file, the default setting. It is useful after a large deletion, rarely otherwise, and needs enough free space for the copy. WP-CLI runs this operation on the whole database.

wp db optimize

The official documentation’s optimization page still advises tuning the MySQL query cache. That advice is outdated: MySQL 8.0 removed that cache, and MariaDB disables it by default because it does not scale well on busy multi-core servers.

The built-in repair tool

WordPress offers a database repair and optimization page, turned on by the WP_ALLOW_REPAIR constant. The documentation warns that, while the constant is on, the page is reachable without logging in. Turn it on for the duration of the operation, then remove it right away.

Indexes

Core adds indexes over the releases: an index on the options autoload column in 5.3, an index on posts by type, status and author in 6.9, for sites with a lot of content. The free Index WP MySQL For Speed plugin adds higher-performance indexes to the metadata and options tables. If it is deactivated without removing its indexes, those indexes stay in place.

Plugins or WP-CLI: which tool for which job

WP-Optimize, from the UpdraftPlus team, installed on more than one million sites, cleans revisions, drafts, trash, spam comments, expired transients and orphaned metadata, with scheduled cleanups. Its Premium version starts at $49 per year for two sites at the time of writing.

Advanced Database Cleaner shows options, metadata, scheduled tasks and tables before any deletion, with their size and autoload mode. Detecting the origin of orphaned data and cleaning Action Scheduler are reserved for its paid version, from $39 per year for one site at the time of writing.

WP-Sweep, free, deletes through WordPress functions rather than raw queries, and offers WP-CLI commands; beware its declared incompatibility with Polylang and WPML. Query Monitor, finally, deletes nothing but spots slow, duplicate or failing queries, and the plugins behind them.

Spotting slow queries

Cleaning the database does not fix a badly written query. Query Monitor, installed on a copy or turned on briefly in production, reports slow and repeated queries, and shows the plugin or theme that triggers them. That is often where the real gain lies: a plugin that queries the metadata table without an index can cost more than thousands of revisions.

For someone comfortable with the command line, WP-CLI covers almost everything, with one advantage: every command can be written down, reread and replayed.

Summary table

Problem	Symptom	Safe move	Risk
Oversized autoloaded options	Site Health alert	Turn off autoloading	Deleting an option still in use
Accumulated transients	Growing options table	Delete expired ones	Slowdown after a full purge
Revisions	Large posts tables	Ceiling and deletion via WP-CLI	Losing a useful history
Orphaned metadata	Heavy metadata table	Count, then clean with a tool	Multilingual false orphans
Action Scheduler queue	Huge job tables	Update, then clean by status	Deleting pending jobs
Disk space not returned	Files that do not shrink	OPTIMIZE after a large deletion	Not enough space for the copy

A maintenance routine

Every month, compare table sizes with your reference figures, check Site Health and, on a store, the Action Scheduler queue. Delete expired transients and revisions beyond your ceiling. Every quarter, audit autoloaded options and data left behind by uninstalled plugins.

These tasks also depend on WordPress scheduled tasks running properly, since they trigger automatic cleanups. Our WordPress maintenance checklist fits this routine into the rest of site upkeep.

Frequently asked questions

Should tables be optimized every week?

No. On InnoDB, optimization rebuilds the table and only helps after a large deletion. A weekly optimization consumes resources with no measurable benefit.

Should options from an uninstalled plugin be deleted?

Yes, if you are sure the plugin will not be reinstalled and the option really belongs to it. When in doubt, turn off its autoloading first: the gain is the same, and you can go back.

Should all tables be converted to InnoDB?

Yes, for the tables of a recent WordPress site, unless the host has a specific constraint. Do it on a copy first, with a backup, and check the available disk space, since the conversion recreates each table.

How many revisions should I keep?

Three to ten are enough for most sites. An editorial site with several reviewers can keep more. What matters is setting a ceiling, since the default is unlimited.

Does an object cache make database cleanup unnecessary?

No. It hides part of the load, but autoloaded options still go through it, and tables keep growing. With an external cache, only transients leave the database: revisions, metadata and queue tables still need watching.

Can the Action Scheduler tables be emptied?

Not entirely. Pending jobs are work still to be done. Delete completed, canceled or failed jobs beyond a certain age, with the dedicated command, and leave pending jobs alone.

Conclusion

Optimizing a WordPress database is less about technique than method: back up, measure, understand where the weight comes from, fix the source, then clean with tools that go through WordPress. The most spectacular commands, emptying a table, optimizing every week, are rarely the most useful.

The WooCommerce 9.8 incident is a reminder: a database often grows because of a behavior, not an oversight. Look at what is filling it before emptying it, and set up a routine that will warn you next time.

Sources

LaFactory designs, builds and maintains WordPress and WooCommerce sites, and develops its own plugins. Talk to us about your WordPress project.

Francis Rozange

Former section editor at Libération, he runs LaFactory, an international web agency since 1996.