Optimise WooCommerce Updates
WooCommerce runs DELETE operations against wp_options on a number of admin and front-end pages, mostly to clear out expired transients. The problem is that those deletes are written in a way that can’t use an index on wp_options. On a small site that’s fine, but once you’ve built up a lot of options, and a busy store accumulates a huge number of transients, MySQL has to scan the whole wp_options table to run them.
While that scan is happening, the table is locked. On our reference site with 820,000+ products this intermittent lockup can last for up to 3 minutes, during which the rest of the site is waiting. It’s one of those problems that doesn’t show up in testing and only appears once your store is large and active.
Because the stall only shows up on a large, busy store and comes and goes, it is one of the hardest performance problems to pin down. Sites often chase caching or hosting for months when the real cause is an unindexed DELETE locking wp_options.
The fix is in the Bloat tab. Set “Optimise WooCommerce Updates” to “Optimise DELETE wp_options operations”. Scalability Pro then rewrites those delete statements so they can use the indexes on wp_options, which turns a multi-minute table scan into a near-instant operation.
Because this changes queries that WooCommerce itself issues, it’s worth enabling on any store where you’ve noticed the site freezing for no obvious reason, particularly if you carry a lot of transients.
On any store with hundreds of thousands of products, or a heavy transient load, turn this on. It only rewrites queries WooCommerce already runs, so there is no behaviour change beyond the deletes becoming fast.
In short: WooCommerce’s transient cleanup deletes can’t use an index and lock wp_options while they scan it, which stalls large stores. Turn on “Optimise WooCommerce Updates” in the Bloat tab and those deletes become indexed and virtually instant.
Comments