Open phpMyAdmin on a WordPress site that nobody has touched in three years and the picture is almost always the same: a 12 MB site whose database has swollen past 400 MB, with wp_options, wp_postmeta and wp_posts doing most of the damage. The content you actually publish is a rounding error. Everything else is sediment — revisions, expired caches, abandoned plugin tables, and autoloaded junk that gets read into memory on every single request. This article is about reading that sediment correctly and removing only the parts that earn their removal.

Find out what is actually big before you delete anything

Generic "speed up your database" advice tells you to clean revisions and call it a day. That is a guess. Measure first. The single most useful query you can run is a per-table size report, which you can get from Adminer or phpMyAdmin, or directly:

SELECT table_name,
       ROUND((data_length + index_length) / 1024 / 1024, 1) AS mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC
LIMIT 15;

The result tells you where to spend effort. On a content site the heavy table is usually wp_posts (revisions) or wp_postmeta. On a WooCommerce store it is frequently wp_postmeta, wp_woocommerce_sessions, or the action scheduler tables. On a site that ran a page builder or analytics plugin for a while, you will often find an abandoned custom table — something like wp_redirection_logs or wp_yoast_indexable — quietly holding hundreds of megabytes. You cannot clean what you have not located, and the location is rarely where the tutorials say it is.

The autoload problem nobody measures

If you fix one thing today, fix this. The wp_options table has an autoload column, and every row marked yes is loaded into PHP memory on every page request, logged-in or not, cached or not. WordPress core expects this value to sit somewhere around 200–800 KB. Plugins that store serialized blobs with autoload left on can push it into multiple megabytes, and now you are deserializing several MB of PHP on every uncached request. That shows up directly in your time to first byte.

Measure it:

SELECT ROUND(SUM(LENGTH(option_value))/1024/1024, 2) AS autoload_mb
FROM wp_options WHERE autoload = 'yes';

SELECT option_name, ROUND(LENGTH(option_value)/1024, 0) AS kb
FROM wp_options WHERE autoload = 'yes'
ORDER BY LENGTH(option_value) DESC LIMIT 20;

The second query names your offenders. Common culprits are old _transient rows that never got an expiry, leftover settings from uninstalled plugins, and bloated option arrays from form or builder plugins. WordPress 6.4+ added a built-in wp_autoload_values_to_autoload() safety net and the autoloaded_options entry in Site Health, so check Tools → Site Health → Info → Database first. To flip a specific oversized option off autoload safely, change the column value rather than deleting the row, and only for options you have identified by name. Do not run a blanket "set everything to no" — some plugins genuinely need autoload and will re-query on every load if you strip it, making things worse.

Revisions: cap them, then trim once

WordPress stores a full copy of post content in wp_posts every time you save a draft or update a published post, and by default it keeps them forever. An article edited across a year of small tweaks can carry 40-plus revision rows. On editorial sites this regularly accounts for the majority of wp_posts.

The fix has two halves. Cap future growth in wp-config.php:

define('WP_POST_REVISIONS', 8);

Keeping a handful is the sane choice — revisions are a genuine recovery feature, and setting the limit to false to disable them entirely trades a real safety net for a small space win. Then trim the historical backlog. WP-CLI is cleaner and safer than a raw DELETE because it fires the proper hooks so related meta is cleaned up too:

wp post list --post_type=revision --format=ids | xargs wp post delete --force

On a large database run it during a quiet window; deleting tens of thousands of rows is I/O heavy. If you must do it in SQL, scope it to revisions only (post_type = 'revision') and never touch auto-draft or inherit rows blindly — attachments also use inherit.

Transients, sessions, and the tables plugins forget to clean

Transients are timed cache entries living in wp_options (unless you run a persistent object cache like Redis, in which case they live there instead — a strong reason to run one). Expired transients are supposed to clear on next access, but ones that are never requested again simply rot in place. wp transient delete --expired clears them; if your transients are in Redis or Memcached this is moot, which is the better state to be in.

WooCommerce deserves special mention because its debt is structural, not incidental. Action Scheduler (wp_actionscheduler_actions and its logs) accumulates completed and failed tasks indefinitely on busy stores — six-figure row counts are common. Clear them from WooCommerce → Status → Scheduled Actions or via WP-CLI, and keep wp_woocommerce_sessions in check, since expired guest sessions linger. These two tables outweigh revisions on most stores, which is exactly why measuring first matters.

Orphaned data and the OPTIMIZE TABLE myth

When a plugin deletes a post without hooking deletion correctly, it strands rows in wp_postmeta, wp_term_relationships, or its own tables. Over years this adds up, and a one-pass cleanup after any major plugin removal is worthwhile. Tools like WP-Optimize and Advanced Database Cleaner surface orphaned meta and dead tables safely; the latter is particularly good at flagging tables belonging to plugins you uninstalled long ago, which manual inspection misses.

One thing to actively skip: obsessive OPTIMIZE TABLE runs. On the InnoDB engine that every modern host uses (MySQL 5.7+/8.0, MariaDB 10.x), OPTIMIZE TABLE performs a full table rebuild that locks or copies the table, and InnoDB already manages free space internally. The measurable payoff is close to zero on a healthy table, and on a large one the rebuild is disruptive. The old advice to "optimize weekly" is a holdover from MyISAM days. Reclaim space by deleting the bloat, not by defragmenting it afterward.

A maintenance rhythm that survives contact with reality

The point of a schedule is that it runs without you babysitting it. Set the cleanup plugin to handle the recurring work and reserve human attention for the audit.

Automated, weekly: purge expired transients, delete spam and trashed comments older than 30 days, and on stores, prune completed Action Scheduler entries.
Hands-on, quarterly: re-run the table-size query, compare it to last quarter, trim revisions past your cap, and re-check the autoload total. A rising autoload number usually means a newly installed plugin needs attention.
Annually, or after any plugin removal: scan for orphaned meta and abandoned custom tables, and confirm nothing is autoloading that shouldn't be.

Back up like you mean it, because cleanup is destructive

Every operation here deletes data. Most are safe; the failure mode, when it happens, is irreversible. Before any cleanup, take a fresh database backup and confirm it is current and restorable — a backup you have never tested is a hope, not a plan. If a cleanup goes wrong, the recovery path is simple precisely because the backup exists: restore, identify the offending step, scope it more tightly, and re-run. Do the dangerous work on staging first when you can, and treat the five minutes spent verifying a backup as the cheapest insurance in WordPress operations.

Maintenance done this way is not glamorous, but it is honest: you measure, you remove what the measurement justifies, you automate the boring parts, and you keep an escape hatch. A database kept on that discipline stays a fraction of the size of a neglected one and answers queries fast enough that you stop thinking about it — which is the entire goal.

WordPress Database Maintenance: What Actually Matters