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.

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.

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.