Delete WordPress Transients to Slim Down wp_options (WP-CLI and SQL)

This article is a localized edition of the Japanese original. Read the Japanese original

Transients (the Transients API) let WordPress core and plugins store results from external APIs or expensive operations for a set period of time. You save them with set_transient() and read them with get_transient().

On sites that do not use an external object cache (such as Redis), transients are stored in the wp_options table. When large numbers of expired transients pile up, the table grows and backups and queries take longer.

How transients are stored

Even on a single site, WordPress core's update check results (such as _site_transient_update_plugins) are stored in wp_options as site transients.

Transients saved with an expiration are not autoloaded, but transients saved without an expiration are autoloaded and never expire. They are not removed by the "delete expired" methods described below, so if there are many of them, find out which plugin creates them.

When expired transients are removed automatically

WordPress deletes expired transients in two situations:

• When one is read with get_transient(). If it has expired, both the value and its expiration are deleted on the spot.
• A daily scheduled task. Since WordPress 4.9, a WP-Cron event called delete_expired_transients runs every day and deletes expired transients in bulk. The event is scheduled when the admin area is loaded.

So you normally do not need to delete them by hand. If large numbers of transients that are no longer read remain (for example, from deactivated or deleted plugins), or WP-Cron is not running, use the methods below.

Back up before you start

What you delete is cache data, but if you run SQL directly, save the database first in case of a typo.

wp db export backup-before-transients.sql

Check the count and size

The SQL below assumes the table prefix is wp_. If yours is different, change wp_options (check it with wp config get table_prefix). In LIKE, _ means "any single character", so it is written as \_ to match the literal character.

# Total number of transients and combined size of their values (MB)
wp db query "SELECT COUNT(*) AS cnt, ROUND(SUM(LENGTH(option_value))/1024/1024, 2) AS mb FROM wp_options WHERE option_name LIKE '\_transient\_%' OR option_name LIKE '\_site\_transient\_%';"

# Number of expired transients
wp db query "SELECT COUNT(*) FROM wp_options WHERE option_name LIKE '\_transient\_timeout\_%' AND option_value < UNIX_TIMESTAMP();"

You can list the existing transients with WP-CLI. With --human-readable, expirations are shown as "39 mins", "expired" and so on.

wp transient list --fields=name,expiration --human-readable

Delete them with WP-CLI (recommended)

# Delete expired transients
wp transient delete --expired

# Delete expired site transients
wp transient delete --expired --network

# Delete a single transient by name
wp transient delete my_plugin_cache

To delete all transients, including ones that have not expired, use --all.

wp transient delete --all
wp transient delete --all --network

--all also removes transients that are actively used as a cache, so right after deleting, plugins rebuild their values and pages may be slower for a while. It is safer to start with --expired.

Delete them with SQL (when WP-CLI is not available)

If you run the query in phpMyAdmin or a similar tool, use SQL in the same form as WordPress core's delete_expired_transients(). It pairs each value row with its expiration row and deletes both when expired.

-- Delete expired transients (both value and expiration)
DELETE a, b FROM wp_options a, wp_options b
WHERE a.option_name LIKE '\_transient\_%'
  AND a.option_name NOT LIKE '\_transient\_timeout\_%'
  AND b.option_name = CONCAT('_transient_timeout_', SUBSTRING(a.option_name, 12))
  AND b.option_value < UNIX_TIMESTAMP();

-- Delete expired site transients on a single site
DELETE a, b FROM wp_options a, wp_options b
WHERE a.option_name LIKE '\_site\_transient\_%'
  AND a.option_name NOT LIKE '\_site\_transient\_timeout\_%'
  AND b.option_name = CONCAT('_site_transient_timeout_', SUBSTRING(a.option_name, 17))
  AND b.option_value < UNIX_TIMESTAMP();

SUBSTRING(a.option_name, 12) extracts the name without the leading _transient_ (11 characters). A DELETE that references the same table in an IN (SELECT ...) subquery fails in MySQL with "You can't specify target table ... for update in FROM clause", so do not use that form.

Delete them from PHP

Calling the WordPress function delete_expired_transients() (WordPress 4.9 and later) does the same thing as the SQL above. From WP-CLI you can run it like this:

wp eval 'delete_expired_transients( true );'

The true argument tells it to clean up the database even when an external object cache is in use.

If you want to delete them on a schedule

WordPress core already deletes them daily, so you do not need to add your own WP-Cron event. On sites with little traffic where WP-Cron rarely runs, or where WP-Cron is disabled, run WP-CLI from the server's cron.

# Example to add with crontab -e (daily at 4:30; adjust the paths to your environment)
30 4 * * * cd /var/www/example.com/public_html && /usr/local/bin/wp transient delete --expired --quiet

If you want to reclaim table space after deleting a large number of rows, run the following command during a quiet period. On InnoDB it rebuilds the table, which can take a while on large tables, so it is not something to run every day.

wp db query "OPTIMIZE TABLE wp_options;"

If you use an object cache such as Redis

When an external object cache is enabled, for example with Redis Object Cache, transients are stored in the cache instead of wp_options, and the cache discards expired ones automatically. In that case, any transients left in wp_options are old ones created before you enabled the cache and are no longer used.

• WordPress core's daily cleanup does not touch the database while an external object cache is in use. Delete the leftover rows with the SQL above or with wp eval 'delete_expired_transients( true );'.
• To set up Redis Object Cache, define constants such as WP_REDIS_HOST in wp-config.php above the line that loads wp-settings.php, then enable the cache under "Settings" → "Redis" in the admin (or with wp redis enable in WP-CLI). The WP_CACHE constant is for page caching and has nothing to do with the object cache.

Summary

• WordPress deletes expired transients automatically when they are read and in a daily scheduled task.
• To delete them manually, use wp transient delete --expired (add --network for site transients).
• When deleting with SQL, use the same DELETE a, b form as WordPress core, which deletes value and expiration rows as pairs.
• Transients without an expiration never expire, so if there are many of them, check which plugin creates them.

For a broader review of autoloaded data in wp_options, see optimizing the wp_options table.

Questions about WordPress? The publisher of this site is based in Japan and replies in Japanese. Contact (Japanese)