Can I remove transients in the wp_options table of my WordPress install?

4 comments

1. You can safetly dump them. WordPress and some plugins will re-create transients as needed. A transient is more or less the stored value from a complex query. The results are saved as a transient so that the system doesn’t have to perform a common query over and over, instead it just looks for the transient if it exists and hasn’t expired. Of course, make a backup of your database before making a change lest something goes wrong!

   After backing everything up, you can run a mysql statement like this:

   DELETE FROM `wp_options` WHERE `option_name` LIKE ('%_transient_%')
   

   [EDIT: statement fixed with escape characters, after comment suggestion]

   Log in to Reply

2. You can delete transients as they will be recreated. There can be buildups of expired transients due to failure situations or design issues with some plugins. One way of coping with this is to remove expired transients while allowing current ones to perform their function. Purging only transients which are expired for a few days gives you a chance to monitor which plugins are resulting in stale transients, and take any action to fix issues or report issues.

   The following will find any wp*option tables in the database and delete the five largest transient options which are more than a week stale. This gives long enough for any plugin to delete options which they are going to purge themselves.

   #!/bin/bash
   
   DBNAME="mydatabase"
   DBUSER="${USER}"
   DBPASSWD="secret"
   MYSQLBIN=/usr/bin/mysql # OR MYSQLBIN=/usr/local/mysql/bin/mysql
   MYSQL="${MYSQLBIN} -s -D ${DBNAME} -u ${DBUSER} -p${DBPASSWD}"
   TMP=/var/tmp/
   ENTRIES_FILE="${TMP}entries.$$"
   
   # Find option tables
   for OPTION_TABLE in $( echo 'show tables like "%wp%options";' | ${MYSQL} )
   do
       # Find up to five large long expired transients
       ${MYSQL} > ${ENTRIES_FILE} <<EOF
       select option_name from ${OPTION_TABLE} where option_name in
           (select concat("_transient",substr(option_name,19))
               FROM ${OPTION_TABLE} WHERE option_name LIKE '_transient_timeout%' AND
               option_value < UTC_TIMESTAMP() - INTERVAL 1 WEEK order by option_value)
       order by length(option_value) desc limit 5;
   EOF
       for OPTION in $( < ${ENTRIES_FILE} )
       do
           echo Deleting ${OPTION} from ${OPTION_TABLE}
           echo delete from ${OPTION_TABLE} where option_name = "${OPTION}"; | ${MYSQL}
           if [[ $? -eq 0 ]]; then
               echo delete from ${OPTION_TABLE} where option_name = "_transient_timeout${OPTION:10}"; | ${MYSQL}
           fi
       done
   done
   rm -f ${ENTRIES_FILE}
   

   Log in to Reply

3. Install the plugin Delete Expired Transients to automatically clean up the database on a daily basis.

   Log in to Reply

4. You can remove transients easily using the WP CLI.

   This can be done with wp transient command, eg.:

   # Delete transient.
   $ wp transient delete sample_key
   Success: Transient deleted.
   
   # Delete expired transients.
   $ wp transient delete --expired
   Success: 12 expired transients deleted from the database.
   
   # Delete all transients.
   $ wp transient delete --all
   Success: 14 transients deleted from the database.
   

   See more in the documentation

   Log in to Reply