Scaling WooCommerce to 100,000 SKUs — The Real Numbers

WooCommerce is not slow. Default WooCommerce is slow. There's a difference, and it's the single most important distinction in this post.

We recently finished a migration for an e-commerce client moving from a legacy Magento 1 store to WooCommerce — 100,000 SKUs, 40+ categories, 12,000 monthly orders, and a hard requirement of sub-second load times on mobile. The baseline install (WooCommerce 9.x, default schema, standard LAMP stack) fell over at around 8,000 products. Page loads hit 6–8 seconds.

Two months later, the same store does 387ms TTFB on product pages, 210ms on category archives, and 340ms on cart updates. Here's how we got there.

Why default WooCommerce slows down

Every product is a WordPress post of type product. Every attribute, price, stock count, and image reference lives in wp_postmeta — the classic EAV table. It's flexible for blogs. It's catastrophic for e-commerce.

At 100,000 SKUs, a single product page in default WooCommerce runs:

• 1 query for the post row
• 40–80 queries for meta values
• 10–20 queries for taxonomies and terms
• 5–15 queries for related products and variations

Multiply by 48 products per category page. You get 500+ queries per request. MySQL doesn't fail — it crawls.

The five-layer architecture we used

Layer 1 — Custom product tables

Instead of fighting wp_postmeta, we moved product data into purpose-built tables. Every storefront column lives in a single wide row. This cut product page queries from 47 to 3.

CREATE TABLE rdelab_products (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  sku           VARCHAR(64)     NOT NULL,
  slug          VARCHAR(191)    NOT NULL,
  name          VARCHAR(255)    NOT NULL,
  price_cents   INT UNSIGNED    NOT NULL DEFAULT 0,
  stock_qty     INT             NOT NULL DEFAULT 0,
  category_id   BIGINT UNSIGNED NOT NULL,
  status        TINYINT         NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  UNIQUE KEY uq_sku (sku),
  KEY idx_category_status (category_id, status),
  KEY idx_price (price_cents)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Layer 2 — Redis object cache

WordPress has no persistent object cache by default. Redis fixed that. Average query count per category page dropped from 480 to 42.

// wp-config.php
define( 'WP_REDIS_HOST', '127.0.0.1' );
define( 'WP_REDIS_PORT', 6379 );
define( 'WP_REDIS_MAXTTL', 86400 );
define( 'WP_CACHE', true );

Layer 3 — Elasticsearch for search

Default Woo search uses LIKE '%term%' — a full table scan. We replaced it with Elasticsearch 8. Search latency went from 2.3s to 38ms.

Layer 4 — Edge cache with tag-based purge

Anonymous product pages cached at Cloudflare edge with 6-hour TTL and tag-based purge. Cache hit ratio: 94.2%. Only 6% of views touch PHP.

Layer 5 — Image optimization

WebP + AVIF via custom resizer with srcset. LCP on category pages dropped from 3.1s to 0.9s.

Before vs after — real benchmarks

What we got wrong

1. Cached too aggressively

24-hour TTL on product pages meant 24-hour price lag. Switched to tag-based purge + 6-hour max TTL. Prices update within 90 seconds.

2. Synced products synchronously

Bulk edits of 500 products took 40 minutes. Moved to Redis-backed queue with 4 workers. Now under 90 seconds.

3. Ignored slow plugins

Query Monitor revealed a "related products" plugin running 140 unindexed queries per page. Removing it took the page from 890ms to 387ms. Every plugin is a liability until proven otherwise.

The stack, in one table

Should you do this?

No, if you have under 10,000 SKUs. The custom table layer is overkill below that. Stick with default WooCommerce + Redis + decent CDN.

Yes, if you're in any of these:

• Above 30,000 SKUs and pages already over 2 seconds
• Doing 1,000+ orders/day and checkout is queuing
• Need faceted search under 100ms
• Migrating from Magento or Shopify Plus
• Host charging enterprise prices for shared-hosting performance

Key takeaways

If you're stuck on WooCommerce performance — or planning a Magento/Shopify migration — we've done this enough times to know the shortcuts. See our WooCommerce development services, or hire our team. We usually reply within 6 hours.