WordPress Database Cleanup and Optimisation

A WordPress database only ever grows. Every draft saves a revision, every plugin leaves options behind when it is removed, every expired transient waits for a cleanup that may never come. None of it breaks anything, which is why it accumulates for years, and then one day the site is sluggish, backups take an age, and nobody can say why. Cleaning up is unglamorous work with an excellent effort-to-reward ratio, and all it needs is an hour, a fresh backup and a little method.

First, look at what is actually in there

Start with evidence, not folklore. wp db size --tables lists every table with its size, and the shape of the result usually tells the story at a glance: an options table measured in hundreds of megabytes, a postmeta table several times larger than posts, or leftover tables from plugins uninstalled years ago. Take a full export before touching anything; every command below is safe, but safe and reversible are different qualities and you want both. Write the numbers down as you go; the point of measuring first is being able to prove afterwards that the work achieved something.

Clearing the usual suspects

# Revisions, usually the biggest single offender
wp post delete $(wp post list --post_type=revision \
  --format=ids) --force

# Expired transients that never got collected
wp transient delete --expired

# Spam and binned comments
wp comment delete $(wp comment list --status=spam \
  --format=ids) --force

Rather than deleting all revisions repeatedly, cap them going forward with the WP_POST_REVISIONS constant; a limit of five keeps the safety net without the archaeology. Orphaned metadata, rows in postmeta whose parent post no longer exists, needs a join to find and is worth doing with a well-reviewed query or a maintenance plugin you trust rather than by hand at midnight. Trashed posts empty themselves after thirty days, but only if cron is actually running, which is worth verifying rather than assuming.

Autoloaded options deserve special attention

Options marked autoload are loaded into memory on every single request, admin and front end alike, cached or not once the request reaches PHP. Plugins routinely mark large settings blobs as autoloaded and never unmark them, and the total creeps upward invisibly.

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

Anything large that belongs to a plugin you no longer run can be deleted; anything large that belongs to a live plugin can usually have autoload switched off, so it loads only when requested. Keeping the combined autoload size modest is one of the most direct database wins available, because it is paid on literally every dynamic request. Recent WordPress versions are smarter about autoloading new options, but historic rows keep whatever flag they were written with, so old sites carry old sins.

Indexes and engines: the bigger levers

Cleanup shrinks the data; indexing changes how it is read. Slow meta queries are endemic because postmeta is keyed for flexibility rather than speed, and on a site with heavy filtering, an index tailored to the hottest query can transform a listing page. Older databases sometimes still carry MyISAM tables worth converting to InnoDB, and it is worth confirming the server's buffer pool actually fits the working set, because no amount of tidying compensates for a database that reads from disk. These changes sit closer to the edge of reversibility, so make them one at a time, measured, and on staging first.

Make it routine, not a rescue

The difference between a healthy database and the one described above is not a heroic one-off cleanup, it is a schedule. We run the safe operations, expired transients, spam, revision pruning, as a monthly WP-CLI script on every site we maintain, with the report going somewhere a human sees it. Reclaim table space with an optimise pass after large deletions, check that uninstalling plugins actually removed their tables, and review autoload totals quarterly. An hour a month keeps the archaeology permanently unnecessary, and keeping the script in version control alongside the site means the routine survives team changes too.

If your database has had a decade of history and no housekeeping, STRCLI can clean it up safely and hand you the routine that keeps it that way.