WooCommerce Performance: Taming Slow Product Queries

WooCommerce is fantastic for small to medium stores, but once you hit 10,000+ products with variations, attributes, and complex filtering, the database starts groaning. I recently optimized a store with 45,000 products that had 6-second page loads on category pages. Here is what worked.

The HPOS Migration

WooCommerce High-Performance Order Storage (HPOS) moves order data from the wp_posts and wp_postmeta tables into dedicated custom tables. This is the single biggest performance win for stores with lots of orders:

// Enable HPOS in wp-config.php or via WooCommerce settings
// WooCommerce → Settings → Advanced → Features → High-Performance Order Storage

On the client store, enabling HPOS reduced order-related query times by 60%. The wp_posts table went from 500,000 rows to 80,000, which sped up every product query as a side effect.

Product Attribute Indexing

WooCommerce stores product attributes in the wp_postmeta table as serialized data. Filtering by attribute requires full table scans. The WooCommerce product attributes lookup table (introduced in WC 6.0+) indexes these properly:

// Regenerate the lookup table
wp wc tool run regenerate_product_attributes_lookup_table

After regenerating this table, attribute filtering queries that took 3-4 seconds dropped to under 200ms.

Disable Cart Fragments on Non-Cart Pages

WooCommerce loads a JavaScript file called cart-fragments.js on every page to update the mini-cart widget via AJAX. On a busy store, this creates an AJAX request on every single page load:

add_action("wp_enqueue_scripts", function () {
    if (!is_cart() && !is_checkout()) {
        wp_dequeue_script("wc-cart-fragments");
    }
});

This removed an unnecessary AJAX call from 90% of page views. On the admin-ajax endpoint alone, this reduced server load by about 40%.

Persistent Object Cache

WooCommerce makes heavy use of the WordPress object cache. Without a persistent cache, all those get_post_meta() calls hit the database repeatedly. Redis or Memcached is not optional for WooCommerce stores — it is mandatory for decent performance.

The combined effect of these optimizations brought the category page load from 6 seconds to 0.8 seconds. The key insight: WooCommerce performance is a database problem, and the solutions are database-focused.