WordPress Database Optimisation: Cleaning Up Safely

This is the single highest-value thing in this article and it's the least known. The wp_options table has an autoload column. Every option marked autoload=yes is loaded into memory on every single request, front end and admin, whether or not anything needs it.

A healthy site has a few hundred kilobytes of autoloaded data. Sites arrive with several megabytes, because plugins store large blobs — cached API responses, licence data, entire settings arrays — with autoload on, and because uninstalled plugins leave theirs behind.

Query the table for the largest autoloaded options and look at what they are. Anything belonging to a plugin you no longer have can go. Anything huge belonging to a plugin you do have is worth raising with its developer, or setting to autoload=no if nothing needs it on every request.

Reducing autoloaded data from three megabytes to three hundred kilobytes is a measurable improvement on every page of the site, and it costs nothing but care.

WordPress saves a revision every time you update a post, forever, by default. A page edited two hundred times carries two hundred rows in wp_posts plus their metadata.

Revisions are genuinely useful — they've rescued plenty of accidentally deleted content — so don't disable them entirely. Cap them instead, with WP_POST_REVISIONS in wp-config.php set to a sensible number like ten or twenty.

Then clean up the historical excess, keeping the most recent few per post. Be deliberate about it: this is content, and deleting the wrong ones is not recoverable without a backup.

Transients are cached values with an expiry. Without a persistent object cache they're stored in wp_options, and WordPress does not reliably clean up expired ones — they sit there until something asks for them again, which for a plugin you removed is never.

Expired transients are always safe to delete. Non-expired ones are safe too; they'll simply be regenerated. On a site with a busy plugin caching API responses, this can be tens of thousands of rows.

The better fix is a persistent object cache, which moves transients out of the database entirely and makes this whole category of problem disappear.

When a post is deleted properly through WordPress, its metadata goes with it. When rows are removed by a script, a broken plugin, or a botched import, the metadata stays — pointing at post IDs that no longer exist.

The same happens in the term relationship tables and in comment meta. None of it is ever read, and all of it makes the tables larger and the queries slower.

This is safe to clean, but it's the kind of cleanup where a wrong join deletes live data. Use a well-established tool rather than hand-writing the SQL, and back up first.

Go through your database's table list and look for tables whose prefix you don't recognise. Each one belongs to a plugin — check whether that plugin is still installed.

• Options in wp_options, often autoloaded, frequently with no recognisable prefix.
• Custom tables, which stay forever unless the plugin's uninstall routine removed them — and many deliberately don't.
• Post meta on every post the plugin ever touched.
• Custom post types' content, still in wp_posts but invisible because nothing registers the type any more.
• Scheduled tasks pointing at functions that no longer exist, which WordPress retries and fails on.

• Spam comments, which on an older site can outnumber legitimate ones by a wide margin. Empty the spam queue and set it to auto-delete after a fortnight.
• Trashed posts and pages, which sit in wp_posts indefinitely. WordPress empties trash after thirty days by default; check that setting wasn't changed.
• Unapproved comments nobody will ever moderate.
• Old form submissions, if a form plugin stores entries in the database — these accumulate fast and are rarely pruned.

Database cleanup is the operation most likely to lose data irreversibly, so the process matters more than the technique.

• Take a full database backup and verify you can restore it. Not the automated one you've never tested — a fresh one.
• Do it on staging first, then check the site thoroughly before repeating on production.
• One category at a time, checking the site between each. If something breaks, you know what caused it.
• Prefer established tools over hand-written SQL. A DELETE with a subtly wrong WHERE clause is very fast and very permanent.
• Do it during quiet hours, since some operations lock tables.
• Note the database size before and after, so you know whether it achieved anything.

WordPress's default indexes are reasonable for typical use, but sites with heavy meta queries — WooCommerce stores in particular — sometimes benefit from an additional index on wp_postmeta.

Don't add indexes speculatively. Each one costs write performance and disk space. Find the actually slow queries first with a query monitoring tool, then index for those specifically.

Optimising tables reclaims space after large deletions, which is worth doing once after a big cleanup. It is not a routine maintenance task and running it weekly achieves nothing.

A slow site is often not a database problem, and cleanup is a satisfying way to spend an afternoon without changing anything a visitor notices.

If the front end is slow but the admin is fine, look at images, scripts and render-blocking CSS instead. If both are slow, check whether a persistent object cache exists before touching the database — that single change usually does more than any amount of cleaning.

Database work is worth doing when the admin is slow, when queries are visibly slow in a profiler, or when autoloaded options are measurably bloated. Otherwise it's housekeeping, not optimisation.