Cleaning Up Stale Transients

WordPress transients are very similar to DB options but they also support expiration times. The Transients API documentation states:

Our transient will die naturally of old age once $expiration seconds have passed since we last ran set_transient()

What you might not know if you haven’t explored the source code of the transients API is that transients stored in the database* will not actually “die of old age”. Instead, a stale transient will stick around until someone tries to access it. Only then will WordPress notice that the transient has expired and delete it. If no-one ever attempts to read a stale transient – e.g. because the plugin that created it has been uninstalled – it will stay in your database forever.

* If you have a suitable caching plugin installed, transients can be stored in a fast in-memory cache like APC or Memcached instead of the database.

Fortunately, since the expiration time of each transient is also stored in the database, we can easily clean up stale transients with a little bit of SQL magic. Run the two queries below to delete all stale transients. You can use either phpMyAdmin or a plugin like WP-DBManager to execute SQL queries.

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,
			CHAR_LENGTH("_transient_") + 1
		)
	)
 AND b.option_value < UNIX_TIMESTAMP()

 

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,
			CHAR_LENGTH("_site_transient_") + 1
		)
	)
 AND b.option_value < UNIX_TIMESTAMP()
Related posts :

6 Responses to “Cleaning Up Stale Transients”

  1. Ron says:

    Thanks a TON for the info! Your trick helped shrink my wp_options table from 886MB to 25MB. Once I ran the two delete queries, I also had to optimize the tables using the command: “mysqlcheck -uroot -p –auto-repair –optimize –all-databases;” to get the space back. I am running WordPress 3.3.2 on CentOS 5.7.

    Thanks again!

  2. Jānis Elsts says:

    Cheers, I’m glad it helped someone. And it is generally a good idea to optimize the database regularly.

  3. Youri says:

    Extremely useful information after a mistake causing DB to fill up with (stale) transients. Thank you!

  4. Thank you for your remarks! Deleting the DB entries is extremely important, but often receives too little attention. Mostly these are unpopular jobs placed on the “to-do” list and gladly put off until tomorrow.

  5. […] den Kollegen von w-shadow.com fanden wir dann folgende hilfreiche SQL-Anweisung: DELETE a, b FROM wp_options a, wp_options […]

Leave a Reply