Huge wp_options table in WordPress

Owning many (and administering even more) WordPress sites, sometimes you come across a database table in which UpdraftPlus cannot backup because it has a ton of rows and it’s not only concerning the backup doesn’t occur but there’s not a simple fix for the problem.

Updraft has a set of extra parameters you can use in wp-config.php to force using sqldmp over PHP dumping with SELECT statements however I couldn’t get them to work (nor find a way to verify the parameters were even being used).

After lots of research, it seems that this may have came across as either transient sessions not being deleted automagically. After checking both the tables and using the Delete Transients Plugin, there was still over 300,000 rows in this particular table.

With more searching, I was able to find in an old Github thread about sessions that expired May 15, 2058 having to due with an old version of Easy Digital Downloads which wasn’t doing garbage collection. Someone had made a plugin addon but it gave a 500 error due to it was searching for edd_options and not wp_options. As this was happening on a production environment, I did a test query in phpMyAdmin after taking a backup.

SELECT * FROM wp_options WHERE option_name LIKE ‘_wp_session_expires_%’ AND option_value+0 < 2789308218

The result, over 300,000 rows.

DELETE FROM wp_options WHERE option_name LIKE ‘_wp_session_expires_%’ AND option_value+0 < 2789308218

After this command, the rows were gone and the database decreased by nearly 25%. Updraft now can backup wp_options without the PHP timeout halting execution.

Will this work for everyone? Probably not, but it did for me. Don’t forget to backup first! 🙂