Auditing wp_options autoload, revisions and transients before you clean a WordPress database

Cleaning a WordPress database is easy to do in the wrong order. The commands that drop revisions and expired transients are one line each, and none of them ask whether the option you just un-autoloaded is read by a plugin on every page load. Audit first, delete second — that order is what lets you undo a mistake without restoring last night's backup.

Everything below runs against a real database. Substitute your own table prefix wherever wp_ appears.

Take a copy you can actually restore from

wp db export pre-cleanup-$(date +%F).sql

Move that file off the server. A dump sitting in the same web root is not a backup. If the site takes inquiries, do this at the quietest hour you have; a dump of a busy InnoDB database is a long read and it holds locks you don't want during business hours.

Then look at where the space actually is:

wp db size --tables --human-readable

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;

If wp_options is near the top of that list on a site with a few hundred posts, the autoload column is the first thing to open.

wp_options: find out what ships with every request

Autoloaded options are read into memory on every request — front end, admin, REST, cron. A large autoloaded payload is a cost you pay whether the visitor needed it or not.

Newer schemas store autoload as on / off / auto-on / auto-off; older ones use yes / no. Check which you have before writing anything else:

SELECT autoload, COUNT(*) FROM wp_options GROUP BY autoload;

Then the total, and the biggest individual rows:

SELECT COUNT(*) AS options,
       ROUND(SUM(LENGTH(option_value)) / 1024, 1) AS kb
FROM wp_options
WHERE autoload IN ('yes', 'on', 'auto-on');

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

For every row near the top you need three answers before changing anything: who owns it, whether it is read on the front end, and whether it regenerates itself.

grep -rn "the_option_name" wp-content/plugins wp-content/themes --include=*.php | head -20

Crude, but it answers the ownership question faster than reading a settings screen. If nothing matches, the option probably belongs to something uninstalled. Note it down and handle it in a second pass, after you have confirmed the site still works without it.

Turning autoload off is one update:

UPDATE wp_options SET autoload = 'off' WHERE option_name = 'the_option_name';

On a schema that still uses yes / no, write 'no'. Then flush:

wp cache flush

Re-run the total query and write the number down. Come back in a week — some plugins call update_option() on save and autoload can get set back on, so an option you turned off today may be back on after the next settings save. Worth knowing before you tell anyone the database is clean.

Revisions: measure by parent, not by total

A revision is a full copy of post_content in wp_posts. The count is less interesting than which posts own the copies.

SELECT COUNT(*) AS revisions,
       ROUND(SUM(LENGTH(post_content)) / 1024 / 1024, 1) AS mb
FROM wp_posts
WHERE post_type = 'revision';

SELECT r.post_parent AS post_id,
       p.post_title,
       COUNT(*) AS revisions,
       ROUND(SUM(LENGTH(r.post_content)) / 1024 / 1024, 1) AS mb
FROM wp_posts r
JOIN wp_posts p ON p.ID = r.post_parent
WHERE r.post_type = 'revision'
GROUP BY r.post_parent, p.post_title
ORDER BY mb DESC
LIMIT 20;

The top of that list is usually one or two long pages that get edited on every sales call. Before deleting anything, note two things:

• Autosaves are stored as revisions too, identified by autosave in post_name. Deleting them does not touch published content, but it will surprise someone who has a draft open in the editor right now.
• Custom post types and page-builder content keep their revisions in the same table, and are deleted the same way.

Delete in chunks so a large site does not exhaust memory or hit command-line argument limits:

while ids=$(wp post list --post_type=revision --format=ids --posts_per_page=200) && [ -n "$ids" ]; do
  wp post delete $ids --force
done

Cap future growth in wp-config.php:

define( 'WP_POST_REVISIONS', 5 );

That is a ceiling going forward. It does not remove what already exists, and it does not shrink the table — see the rebuild step at the end.

Transients: expired is safe, orphaned needs a decision

With no persistent object cache, transients live in wp_options as a _transient_<key> row plus a _transient_timeout_<key> row.

Count the expired ones:

SELECT COUNT(*) AS expired
FROM wp_options
WHERE option_name LIKE '\_transient\_timeout\_%'
  AND option_value < UNIX_TIMESTAMP();

Those are dead. WordPress clears them on its own schedule eventually; doing it now is safe:

wp transient delete --expired

Orphans are a different case — a value row with no timeout row at all:

SELECT COUNT(*) AS orphans
FROM wp_options o
WHERE o.option_name LIKE '\_transient\_%'
  AND o.option_name NOT LIKE '\_transient\_timeout\_%'
  AND NOT EXISTS (
    SELECT 1 FROM wp_options t
    WHERE t.option_name = CONCAT('_transient_timeout_', SUBSTRING(o.option_name, 12))
  );

Some were set without an expiry on purpose; some were left behind by a plugin that stopped writing to them. Deleting an orphan costs a cache miss the next time the plugin asks for the value, so the risk is low. If the number is large, find the owning prefix first — a plugin that keeps state in a no-expiry transient may behave differently right after the flush.

One check worth doing before you count these as waste: if the site runs Redis or Memcached, transients live in the object cache and these option rows are leftovers from before it was enabled. Flush and re-count to find out which world you are in.

wp cache flush

Rebuild the tables and verify

Deleting rows does not shrink the files. On InnoDB tables with a lot of free space, a rebuild reclaims it:

wp db optimize

This is not free. It rebuilds tables and takes time proportional to their size, so run it in a quiet window rather than at 2pm on a Tuesday.

Verify in this order:

wp option get siteurl
wp post list --post_type=page --fields=ID,post_title,post_status --posts_per_page=5
wp plugin list --status=active

Then in a browser, with a cache-busting query string: the home page, the page that had the most revisions, the inquiry or checkout form, and the admin dashboard. Watch the browser console for 404s, and open the settings screen of any plugin whose options you touched — that is where a wrongly deleted option shows up first.

Re-measure what you measured at the start:

SELECT COUNT(*) AS options,
       ROUND(SUM(LENGTH(option_value)) / 1024, 1) AS kb
FROM wp_options
WHERE autoload IN ('yes', 'on', 'auto-on');

Compare the two numbers. If the autoloaded total barely moved, the database was not your bottleneck and the next place to look is the slow query log, not another cleanup plugin.

The order that holds up: export, measure, identify the owner, change one class of rows at a time, verify the front end, rebuild, re-measure. Every step you skip is a step you redo from the backup.

If you want an outside read on what the site actually serves after the rebuild — broken assets, redirect chains, pages that no longer resolve — a site check at https://aumcreate.com/tools/site-check?utm_source=devto&utm_medium=content&utm_campaign=devto-wp-db-cleanup-order covers that from the visitor's side.