Clean Up Autoloaded wp_options Bloat (Drop TTFB in One Afternoon)

WordPress reads every row in wp_options with autoload='yes' into PHP memory on every single page load. On a neglected site that can be 10+ MB of expired transients, orphaned plugin settings, and debug dumps — multiply by page views and you’ve got seven-figure daily waste. This snippet ships the SQL audit, the cleanup routine, and a scheduled mu-plugin that keeps the table clean forever.

The worst case I’ve audited was a WooCommerce store with 127 plugins installed over 6 years. wp_options had 4,200 autoloaded rows totalling 14.2 MB. TTFB averaged 1.4 seconds on a well-specced VPS, even with object caching. After running the cleanup below — removing 3,100 expired transients, de-autoloading 280 plugin options that only load once a day, and deleting 40-odd rows from uninstalled plugins — autoloaded data dropped to 420 KB and TTFB settled at 190ms. Two hours of work, no plugin installed, permanent win. Every WordPress site that has been live for more than 12 months has this problem to some degree. The snippet below is the exact audit + cleanup workflow I run during every performance engagement.

What this snippet does

• Reports the current autoloaded total size with a single SQL query you can run from phpMyAdmin, Adminer, or WP-CLI — one number tells you if you have a problem
• Lists the top 20 fattest autoloaded rows so you can see exactly which plugins are hoarding memory
• Finds and deletes every expired transient safely via delete_expired_transients()
• Identifies options named after deactivated or removed plugins and lets you bulk-delete them after a manual review
• De-autoloads options that don’t need to be in memory on every request (update_option() with the third argument set to no) — keeps the data, stops loading it until a function explicitly asks for it
• Schedules a weekly mu-plugin cron that repeats the expired-transient purge so the problem doesn’t grow back
• Logs a before/after delta to a mu-plugin log file so you can see the impact in numbers
• Works on shared hosting, VPS, managed WordPress — pure SQL and WordPress core APIs, no plugin activation required
• Safe with object caching: Redis, Memcached, and WP Engine’s object cache all still work correctly since we touch wp_options directly and then flush the cache in one call

The audit — run this first

Before deleting anything, see what you have. These four SQL queries give you the total, the top offenders, and the fixable subsets. Run them in phpMyAdmin, Adminer, or via wp db query. Do not skip the audit — blindly deleting options breaks sites, understanding what you have before you cut doesn’t.

-- ============================================================
-- STEP 1: How big is the autoloaded problem?
-- ============================================================
SELECT 
  COUNT(*) AS row_count,
  ROUND( SUM( LENGTH(option_value) ) / 1024 / 1024, 2 ) AS size_mb,
  ROUND( AVG( LENGTH(option_value) ), 0 ) AS avg_bytes
FROM wp_options 
WHERE autoload = 'yes';

-- Anything over 1 MB is worth a pass. Over 3 MB is a problem. Over 8 MB
-- is an emergency and your TTFB is already hurting.


-- ============================================================
-- STEP 2: The top 20 fattest autoloaded rows
-- ============================================================
SELECT 
  option_name,
  ROUND( LENGTH(option_value) / 1024, 1 ) AS size_kb
FROM wp_options 
WHERE autoload = 'yes' 
ORDER BY LENGTH(option_value) DESC 
LIMIT 20;

-- Common culprits: transient values that refused to expire, _site_transient_
-- update_plugins with entries for plugins you deleted, cron arrays from plugins
-- still firing hooks nothing handles, WooCommerce admin notices meta.


-- ============================================================
-- STEP 3: Find expired transients (these are ALWAYS safe to delete)
-- ============================================================
SELECT COUNT(*) AS expired_count, ROUND( SUM( LENGTH(option_value) ) / 1024 / 1024, 2 ) AS size_mb
FROM wp_options 
WHERE option_name LIKE '\_transient\_timeout\_%' 
  AND option_value < UNIX_TIMESTAMP(NOW());


-- ============================================================
-- STEP 4: Find options from plugins that no longer exist
-- Shows every autoloaded row prefixed with common plugin slugs — review before deleting
-- ============================================================
SELECT option_name, ROUND( LENGTH(option_value) / 1024, 1 ) AS size_kb
FROM wp_options
WHERE autoload = 'yes'
  AND option_name REGEXP '^(wpcf7_|wpseo_|et_|elementor_|vc_|avada_|divi_|ct_|gk_|wpbakery|thrive_|optin_|monarch_|bloom_)'
ORDER BY LENGTH(option_value) DESC;

The cleanup — PHP, reversible, safe

Once the audit shows you what’s there, the cleanup script does the actual work. Three operations, in order of safety. (1) delete_expired_transients() — WordPress ships this core function. It purges every transient whose timeout has passed. Safe, reversible by definition (expired data was never going to be read anyway). (2) De-autoload heavy options — use update_option($name, $value, 'no') which rewrites the row with autoload='no'. The data is preserved; it just stops loading into memory on every request. Anything that calls get_option() still works, because get_option() does its own SELECT when the option isn’t in the autoloaded cache. This is the biggest wins/lowest risk step. (3) Delete options for plugins that no longer exist — the riskiest step, requires manual review of the audit output. If you see options prefixed with a plugin you uninstalled (for example wpseo_* from an old Yoast install), delete them with delete_option(). Always take a backup first; you cannot restore a deleted option without the original value. After cleanup, call wp_cache_flush() so your object cache doesn’t serve the old bloated value.

The cleanup script + scheduled mu-plugin

• Full cleanup script with audit + de-autoload + delete: gist.github.com/wpgaurav — search gt-options-cleanup
• Scheduled mu-plugin that runs delete_expired_transients() every Sunday at 03:00 server time, bundled in the Functionalities plugin
• WP-CLI one-liner to view autoload stats: wp db query "SELECT COUNT(*), ROUND(SUM(LENGTH(option_value))/1024/1024, 2) AS mb FROM wp_options WHERE autoload='yes'"
• WP-CLI for transient cleanup: wp transient delete --expired
• Query Monitor (wordpress.org) — gives you a real-time view of autoloaded options per request, essential for identifying which plugin is bloating on your next audit

FAQs

Related snippets

• Stop wp-cron from firing on every page load — the other first-hour WordPress performance fix
• All PHP snippets
• Functionalities plugin