Expired Transients in the DB: Cleanup, Optimisation and Hygiene

Transients are WordPress's internal cache - small values stored in the DB with an expiry. They are great for caching expensive query results, but the cleanup mechanism is lazy: WordPress only deletes an expired transient when something tries to read it. Transients that are never read again sit forever.

Why this matters

On a long-running site with many plugins, the wp_options table can swell to tens of thousands of rows, most of them dead transients. Concrete impact:

• DB size: backups take longer, and the InnoDB file does not shrink automatically when rows are deleted (use OPTIMIZE TABLE to compact).
• Slow queries: every time WordPress loads autoload options - which happens on every request - it scans the table. Extra rows slow that scan.
• Statistical signal: a site with 50,000+ stuck transients almost always has a plugin that creates transients on every request without cleanup - an architectural problem worth fixing.

It is not an immediate-performance crisis, but periodic hygiene is cheap and worthwhile.

How to detect

Count expired transients via SQL in phpMyAdmin:

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

WP-CLI alternative:

wp transient list --expired --format=count

RankPlus counts the same rows and flags any total above the threshold (typically 1,000+).

How to fix

1. The simplest path: wp transient delete --expired. This removes only expired entries and leaves valid ones alone.
2. UI alternative: WP-Optimize or Transients Manager expose a Delete Expired button and can schedule weekly cleanups.
3. SQL alternative:

   DELETE FROM wp_options WHERE option_name LIKE '\_transient\_timeout\_%' AND option_value < UNIX_TIMESTAMP();
   DELETE FROM wp_options WHERE option_name LIKE '\_transient\_%' AND option_name NOT LIKE '\_transient\_timeout\_%' AND NOT EXISTS (SELECT 1 FROM (SELECT option_name FROM wp_options) b WHERE b.option_name = CONCAT('_transient_timeout_', SUBSTRING(option_name, 12)));

4. After cleanup, compact the InnoDB file: OPTIMIZE TABLE wp_options;. This actually reclaims space that is logically deleted but still held in the tablespace.
5. Schedule it: WP-Optimize can run weekly via WP-Cron, or add a server cron entry:

   0 3 * * 0 cd /var/www/html && wp transient delete --expired --allow-root

6. If the problem returns quickly, find the offender: wp transient list | head -20 - the transient name usually maps to a plugin (often analytics or a poorly managed cache).

Common mistakes

• Deleting all transients (--all): valid ones get wiped too, and WordPress recomputes them on demand - sometimes briefly slowing the site. Stick to --expired.
• Skipping OPTIMIZE TABLE: rows are logically removed but the InnoDB file is still the same size. Running OPTIMIZE actually frees space.
• Re-running cleanup without finding the source: a plugin generating 50,000 transients a week will refill within days.
• Cleaning on a site with object cache (Redis/Memcached): transients live in memory there, not in the DB - SQL cleanup is irrelevant. Use wp transient delete --expired which respects the active backend.

Verifying the fix

Re-run the expired-transient count - it should be zero or close. Check wp_options size with SHOW TABLE STATUS LIKE 'wp_options'; and look at Data_length. RankPlus returns to green. Add a scheduled cleanup so you do not have to repeat this manually.