I’ve got a pair of wordpress sites under my care that have an issue with the wp_options table getting bloated. I mean, really big. Millions of rows for a modest sized site. I suspect it has something to do with the Yoast plugin but I’m not 100% sure. At some point I need to figure out WHY this happens but in the meantime I’ve just been treating the symptoms, so to speak. Every month or so I go in and clean up the tables.
Basically what is happening is that a bunch of transient records aren’t expiring or getting deleted or whatever they are supposed to be doing.
Live example from one of these sites (which, I should add, is running on MySQL)
select COUNT(*) from wp_options
2284298
I’ve actually seen it bigger.
So how many of these records are transients?
select COUNT(*) FROM `wp_options` WHERE `option_name` LIKE ('%\_transient\_%')
2274527
So all but 9,771 rows are transients. Even 10K rows seems pretty big for wp_options but for now I’ll pick my battles.
So time to clean up. Step 1 is always to backup the database first, or AT LEAST backup the wp_options table. I prefer to wait to do this late at night or very early in the morning, just in case it impacts performance.
Once you have a backup, do the cleanup
DELETE FROM `wp_options` WHERE `option_name` LIKE ('%\_transient\_%')
Then recheck the count:
select COUNT(*) from wp_options
10054
Much better. And now we’re set for another month or two.
As I said, I’ve been having to do this for a couple sites for a while now, and so far haven’t seen any negative impact from nuking the transients, but as always, I don’t assume responsibility for any damage you cause to your site!
