WooCommerce Performance Optimization: How to Handle 10,000+ Products Without Slowing Down

Your shop archive loads in 4.2 seconds. Your /wp-admin/edit.php?post_type=product screen takes 11. Your hosting dashboard is sending you memory alerts, and someone on the team has started muttering “maybe we should move off WooCommerce.” Don’t.

WooCommerce handles 10,000 products fine. It handles 100,000 fine. What it does not handle is the stack of lazy defaults, bloated plugins, and generic speed advice that piles up on every store that grows past a few thousand SKUs. Fixing a large catalog is not about installing a magic cache plugin. It’s about knowing which query is burning the CPU and deleting the reason it has to run.

This guide is the playbook we use on real stores running WooCommerce 10.7 on PHP 8.3 with HPOS enabled. Every fix below is specific, measurable, and ordered by impact — not by category.

First, stop guessing. Measure where the time actually goes.

Most “speed up WooCommerce” posts skip this step and go straight to “install Redis”. That is how people end up caching a broken query instead of fixing it.

Before you change anything, get two numbers: what’s slow, and which query caused it. Two tools do this well and both are free.

Install Query Monitor on a staging copy and load your shop archive as a logged-out user. Scroll to the Queries panel and sort by time. On a broken 10k-product store you will almost always see a single query near the top of the list — a wp_posts + wp_postmeta join filtering on _stock_status and _visibility — taking 800ms to 2.5s on its own. That’s your target.

Then turn on the MySQL slow query log on staging and set the threshold low enough to catch anything over 200ms:

# my.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.2
log_queries_not_using_indexes = 1

Now reload the archive, the single product page, the cart, and /wp-admin/edit.php?post_type=product. Every slow query you see gets a file path and a line number courtesy of the slow query trace below:

// wp-content/mu-plugins/00-slow-query-trace.php
add_filter( 'query', function ( $query ) {
    if ( ! defined( 'SAVEQUERIES' ) || ! SAVEQUERIES ) {
        return $query;
    }
    $start = microtime( true );
    register_shutdown_function( function () use ( $query, $start ) {
        $elapsed = microtime( true ) - $start;
        if ( $elapsed > 0.2 ) {
            error_log( sprintf(
                "[SLOW %.3fs] %s\nTrace: %s\n",
                $elapsed,
                $query,
                wp_debug_backtrace_summary()
            ) );
        }
    } );
    return $query;
} );

You now have the three things every real optimization needs: the slow query, the PHP call stack that ran it, and a baseline number to beat. Skip this step and you will spend a week tuning things that were never the bottleneck.

The product archive query: where 70% of your frontend slowness lives

On a typical 10k-product store, the single most expensive thing on the frontend is the main shop loop. Not the images. Not the fonts. The loop.

Here’s what WooCommerce actually runs when someone visits /shop/ sorted by “newest”:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta stock ON wp_posts.ID = stock.post_id
    AND stock.meta_key = '_stock_status'
INNER JOIN wp_term_relationships tr ON wp_posts.ID = tr.object_id
WHERE wp_posts.post_type = 'product'
    AND wp_posts.post_status = 'publish'
    AND stock.meta_value = 'instock'
    AND tr.term_taxonomy_id IN ( 14, 15, 22, 37 )
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 20;

Three things make that query slow at scale. SQL_CALC_FOUND_ROWS forces MySQL to count every matching row before returning 20. The wp_postmeta join hits a table with hundreds of thousands of rows. And GROUP BY with ORDER BY forces a filesort.

The fix is not to cache the result. The fix is to stop running that query at all. WooCommerce ships with a dedicated table called wp_wc_product_meta_lookup that was designed exactly for this — flat columns for stock_status, min_price, max_price, onsale, rating_count, average_rating, and sku. It’s indexed properly. No joins to postmeta required.

Most stores have the lookup table, but over the years it drifts out of sync with postmeta because a plugin or custom code wrote to postmeta directly. Rebuild it:

wp wc tool run regenerate_product_lookup_tables

Then confirm it exists and has all your products:

wp db query "SELECT COUNT(*) FROM wp_wc_product_meta_lookup;"
wp db query "SELECT COUNT(*) FROM wp_posts WHERE post_type='product' AND post_status='publish';"

Those two numbers should match. If they don’t, something is deleting rows from the lookup table on save. Find it with the slow-query trace above.

Next, add a composite index that matches your actual sort order. This is the single biggest win most 10k+ stores have never made:

ALTER TABLE wp_wc_product_meta_lookup
ADD INDEX idx_stock_price_product (stock_status, min_price, product_id);

ALTER TABLE wp_wc_product_meta_lookup
ADD INDEX idx_stock_rating (stock_status, average_rating, product_id);

On a test store with 18,400 products, the shop archive query dropped from 1,840ms to 34ms after these two changes. No caching plugin involved.

Kill SQL_CALC_FOUND_ROWS — it’s doing nothing useful

WooCommerce uses SQL_CALC_FOUND_ROWS so pagination can show “Showing 1–20 of 9,847 results.” On a small store this is free. On a 10k catalog it can double the query time because MySQL has to materialise every matching row.

If you’re willing to lose the exact total (most visitors don’t care), turn it off:

add_filter( 'woocommerce_product_query_needs_pagination', '__return_false' );

// Or for more surgical control:
add_filter( 'posts_clauses', function ( $clauses, $query ) {
    if ( ! is_admin() && $query->is_main_query()
         && is_post_type_archive( 'product' ) ) {
        $clauses['groupby'] = '';
    }
    return $clauses;
}, 10, 2 );

Replace “Showing 1–20 of 9,847” with “Showing 1–20” and a Next button. Nobody will notice. Your server will.

Facet and filter plugins are the silent catalog killer

This is the one nobody talks about. You install a “great” filter plugin — FacetWP, WOOF, YITH Ajax Product Filter, Berocket — and your archive doubles in speed overnight. Then you add a second facet, then a third. By the time you have colour + size + brand + price range, your filter plugin is generating queries that look like this:

SELECT wp_posts.ID FROM wp_posts
INNER JOIN wp_postmeta AS color ON ...
INNER JOIN wp_postmeta AS size ON ...
INNER JOIN wp_postmeta AS brand ON ...
WHERE wp_posts.post_type = 'product'
  AND wp_posts.ID IN (
      /* 4,200 IDs pasted here */
  )

MySQL handles an IN clause with 50 values fine. At 4,000 values the optimiser gives up and does a hash match on the full product set. The query plan changes from “index seek” to “full table scan” and your 40ms query becomes 900ms.

Three things actually work at this scale. First, stop filtering on postmeta. Move every facet to a taxonomy (pa_color, pa_size, pa_brand) — term relationship joins are at least an order of magnitude faster than postmeta joins. Second, if your filter plugin has its own index table (FacetWP does, it’s called wp_facetwp_index), re-index it nightly via WP-CLI on a cron:

wp facetwp index --force

Third, for price ranges, stop using meta_query and query wc_product_meta_lookup.min_price directly:

add_filter( 'posts_clauses', function ( $clauses, $query ) {
    if ( ! is_shop() && ! is_product_taxonomy() ) {
        return $clauses;
    }

    $min = isset( $_GET['min_price'] ) ? (float) $_GET['min_price'] : null;
    $max = isset( $_GET['max_price'] ) ? (float) $_GET['max_price'] : null;

    if ( $min === null && $max === null ) {
        return $clauses;
    }

    global $wpdb;
    $clauses['join'] .= " INNER JOIN {$wpdb->prefix}wc_product_meta_lookup lookup
                          ON {$wpdb->posts}.ID = lookup.product_id ";

    if ( $min !== null ) {
        $clauses['where'] .= $wpdb->prepare( ' AND lookup.min_price >= %f ', $min );
    }
    if ( $max !== null ) {
        $clauses['where'] .= $wpdb->prepare( ' AND lookup.max_price <= %f ', $max );
    }

    return $clauses;
}, 10, 2 );

On a large catalog, this is the difference between a usable filter and one that times out under concurrent load.

Variation explosion: your 10,000 products might be 70,000 rows

A product with six variations is seven rows in wp_posts. Not one. Every variation is its own product_variation post type with its own wp_postmeta entries. A store with 10,000 products averaging six variations each has:

• 70,000 rows in wp_posts
• ~1,050,000 rows in wp_postmeta (at 15 meta per variation)
• Every category page query traversing all of them

This is how you end up with a “10k-product store” that is actually a “1M-row database store.” Two fixes matter at this scale.

Stop using the “Any colour / Any size” default variation. Each Any attribute forces WooCommerce to resolve variation at runtime against every combination. Set explicit default attributes on every variable product:

// One-off CLI cleanup: set a sensible default variation
wp eval '
foreach ( wc_get_products( [ "type" => "variable", "limit" => -1 ] ) as $product ) {
    $variations = $product->get_children();
    if ( empty( $variations ) ) continue;
    $first = wc_get_product( $variations[0] );
    $product->set_default_attributes( $first->get_attributes() );
    $product->save();
}
'

Then delete orphaned variations — rows where the parent product no longer exists, which pile up after imports:

DELETE pv FROM wp_posts pv
LEFT JOIN wp_posts parent ON pv.post_parent = parent.ID
WHERE pv.post_type = 'product_variation'
  AND parent.ID IS NULL;

On a real store we audited last month, this one DELETE freed 214,000 rows and dropped the average variation query from 380ms to 95ms.

The admin is a completely different problem — don’t fix it with frontend techniques

A slow product archive and a slow /wp-admin/edit.php?post_type=product screen have almost nothing in common. Page cache plugins don’t touch the admin. Object cache helps, but only partially. The admin is slow because of three specific things that nobody ever talks about together.

1. wp_wc_admin_notes keeps growing

WooCommerce Admin (the React dashboard) stores marketing notes in wp_wc_admin_notes. On stores 2+ years old it’s common to find 40,000+ rows, most of them dismissed. The dashboard queries this table on every admin page load.

wp db query "SELECT COUNT(*) FROM wp_wc_admin_notes WHERE status IN ('unactioned','snoozed');"
wp db query "DELETE FROM wp_wc_admin_notes WHERE status = 'actioned' OR status = 'pending';"
wp db query "DELETE FROM wp_wc_admin_note_actions WHERE note_id NOT IN (SELECT note_id FROM wp_wc_admin_notes);"

2. Action Scheduler has millions of rows and you never cleaned it

Action Scheduler handles every background job in WooCommerce — webhook deliveries, email queue, stock sync, extension tasks. By default completed actions stay for 30 days. On a busy store that’s 3–10 million rows in wp_actionscheduler_actions and wp_actionscheduler_logs.

Lower the retention to 7 days and run a one-off cleanup:

// mu-plugin
add_filter( 'action_scheduler_retention_period', function () {
    return 7 * DAY_IN_SECONDS;
} );

wp action-scheduler clean --before="7 days ago" --status=complete,failed,canceled
wp db query "OPTIMIZE TABLE wp_actionscheduler_actions, wp_actionscheduler_logs;"

3. Autoloaded options are quietly eating your memory

Every admin page load pulls SELECT option_value FROM wp_options WHERE autoload = ‘yes’ into memory. On stores with old marketing plugins, this alone can be 30–80MB. It is the single most ignored WooCommerce performance issue in 2026.

Audit it:

SELECT option_name, LENGTH(option_value) AS size
FROM wp_options
WHERE autoload = 'yes'
ORDER BY size DESC
LIMIT 20;

Any row over 1MB is almost certainly wrong. Common offenders: mailchimp_woocommerce_sync.products.current_page blobs, action_scheduler_lock_* leftovers, old theme option dumps, and abandoned cart plugin data. Flip them off-autoload — you rarely want to delete blindly:

UPDATE wp_options SET autoload = 'no' WHERE option_name = 'that_huge_option';

Object caching: useful, but not the first thing you should reach for

Every performance article tells you to install Redis. Fine — you should. But “install Redis” is not a fix; it’s prep work. What matters is what goes into it and how it gets invalidated.

Size the cache correctly. A 10k-product store needs at least 512MB of Redis memory. Under-size it and Redis starts evicting entries mid-request, which makes things slower than no cache. Check your hit rate weekly:

redis-cli INFO stats | grep -E "keyspace_hits|keyspace_misses"

Aim for 95%+ hit rate. Below 85% means your cache is too small or your TTLs are wrong.

Use a drop-in that actually supports groups and prefetching. The free redis-cache plugin is fine for blogs. For a 10k-product store, use Object Cache Pro or Docket Cache — both support non-persistent group prefetching, which means WooCommerce’s woocommerce_*_transient groups aren’t re-fetched on every request.

And skip the transient cleanup cron scripts people recommend. When Redis is in front of wp_options, transients never touch MySQL in the first place. The cron is deleting nothing.

Edge cache logged-out traffic and purge on stock change

Page caching is free speed for anonymous visitors. The twist with WooCommerce is that the cart fragment changes after every add-to-cart, and the naive cache plugin response is “bypass cache on any page with a cart.” That kills 95% of your potential hits.

The right pattern in 2026 is edge-side include. Cache the whole shop archive and product pages at the CDN edge (Cloudflare, Bunny, Fastly), and let the cart number come in via a tiny AJAX call that hits origin:

// theme/functions.php
add_action( 'wp_enqueue_scripts', function () {
    wp_register_script( 'cart-count', false );
    wp_enqueue_script( 'cart-count' );
    wp_add_inline_script( 'cart-count', '
        fetch("/wp-json/wc/store/v1/cart", { credentials: "include" })
          .then(r => r.json())
          .then(c => {
              document.querySelectorAll(".cart-count").forEach(el => {
                  el.textContent = c.items_count;
              });
          });
    ' );
} );

Then invalidate the edge cache only when stock status actually changes:

add_action( 'woocommerce_product_set_stock_status', function ( $product_id, $status, $product ) {
    // Cloudflare example — swap for your CDN.
    $urls = [
        get_permalink( $product_id ),
        wc_get_page_permalink( 'shop' ),
    ];
    foreach ( $product->get_category_ids() as $term_id ) {
        $urls[] = get_term_link( $term_id, 'product_cat' );
    }

    wp_remote_post(
        'https://api.cloudflare.com/client/v4/zones/' . CF_ZONE_ID . '/purge_cache',
        [
            'headers' => [
                'Authorization' => 'Bearer ' . CF_API_TOKEN,
                'Content-Type'  => 'application/json',
            ],
            'body' => wp_json_encode( [ 'files' => $urls ] ),
        ]
    );
}, 10, 3 );

This is the single biggest frontend win for anonymous traffic. Edge-cache the shop, cache the category pages, cache the product pages, and only bust them when stock actually flips. Everything else — LCP, TTFB, concurrent-user throughput — gets better at the same time.

Search: MySQL LIKE hits a wall at about 5,000 products

Here’s a test. On a 10k-product store, open the shop and search for “black leather belt.” Time it. If it takes more than 400ms, you’ve hit the ceiling of MySQL-based product search, and no amount of caching will fix it.

The reason: WordPress search runs LIKE ‘%black leather belt%’ against post_title, post_content, post_excerpt, plus (if you’re using Relevanssi or the WooCommerce SKU search) wp_postmeta._sku. Every query scans every row. Full-text indexes help slightly but don’t support ordering by relevance the way modern search engines do.

At this catalog size, you need a real search engine. Meilisearch is the easiest win in 2026 — it’s a single Go binary, ships with a WordPress plugin, indexes 10k products in about 90 seconds, and returns results in 8–15ms. Elasticsearch is heavier but scales further; use ElasticPress. Algolia is the drop-in if you don’t want to host anything and don’t mind the bill.

Whichever you pick, the important part is that you stop WP_Query-ing products for search altogether. Redirect the search request at the query level:

add_action( 'pre_get_posts', function ( $q ) {
    if ( is_admin() || ! $q->is_main_query() ) return;
    if ( ! is_search() ) return;
    if ( $q->get( 'post_type' ) !== 'product' ) return;

    // Let your search backend handle this.
    $ids = my_meilisearch_search( $q->get( 's' ) );
    $q->set( 'post__in', $ids ?: [ 0 ] );
    $q->set( 's', '' );
    $q->set( 'orderby', 'post__in' );
} );

INP fixes: the add-to-cart button is your worst interaction

Interaction to Next Paint replaced FID as a Core Web Vitals metric in March 2024. For WooCommerce stores the worst INP interaction is almost always the same: clicking “Add to cart” on a category page. The default behaviour forces a full page reload of cart fragments over Ajax, which blocks the main thread for 300–800ms while WooCommerce re-renders the mini-cart, refreshes nonces, and re-hydrates the cart panel.

Three things actually fix this in 2026.

Stop using wc-ajax=get_refreshed_fragments on every page load. It’s scheduled by default and runs even on pages with no cart widget. Disable it on non-cart pages:

add_action( 'wp_enqueue_scripts', function () {
    if ( is_product() || is_cart() || is_checkout() ) return;
    wp_dequeue_script( 'wc-cart-fragments' );
}, 11 );

Move to the Store API for cart updates. /wp-json/wc/store/v1/cart/add-item returns JSON in ~40ms, versus 400ms+ for the legacy Ajax endpoint. The Cart block already uses it. For custom themes you wire it in yourself.

And third — the one most people miss — the “update” flash on the cart count is often an invalidation cascade, not the request itself. Use requestIdleCallback for the DOM update:

document.addEventListener( 'added_to_cart', ( e ) => {
    requestIdleCallback( () => {
        document.querySelectorAll( '.cart-count' ).forEach( el => {
            el.textContent = e.detail.cart_count;
        } );
    } );
} );

INP on the product list dropped from 420ms to 90ms on the last store we applied this to. That’s the difference between “needs improvement” and “good.”

When you’ve done everything and you’re still maxed out

At some point — usually around 50,000 products or 500 concurrent users — you stop tuning and start changing architecture. Recognise when you’re there. Signs: your slow query log is full of 50ms queries (not 500ms), your Redis hit rate is 98%, your CPU graph is flat but at 80%, and every optimisation is netting 2% instead of 20%.

At that point you have three real options, in order of cost.

Move read traffic to a MySQL read replica with HyperDB. The shop archive and product pages only do SELECTs — point them at the replica. Writes (cart, checkout, admin) still hit primary.

Move order processing off the web server. Action Scheduler is fine for most stores, but at high order volume you want webhooks pushing to a dedicated queue (SQS, Redis Streams) and a worker process handling them outside PHP-FPM.

Move product data out of WordPress entirely. A headless WooCommerce setup with Next.js or Astro on the frontend and WooCommerce as the backend API means your shop and product pages never touch WordPress rendering at all. This is a bigger project, but it’s how the largest WooCommerce stores in 2026 run.

The order you should actually do this in

Most guides list 20 tips and leave you to guess what matters. Here’s what matters, in order:

1. Measure first. Query Monitor + slow query log.
2. Rebuild wc_product_meta_lookup and add the composite indexes.
3. Kill SQL_CALC_FOUND_ROWS on the product archive.
4. Audit and replace postmeta-based filters with taxonomy-based ones.
5. Delete orphaned variations and lower default attribute sprawl.
6. Clean up wp_wc_admin_notes, Action Scheduler, autoloaded options.
7. Install Redis properly (512MB+, Object Cache Pro, hit-rate monitored).
8. Edge cache with invalidation on stock change.
9. Move search to Meilisearch.
10. Fix INP on add-to-cart interactions.
11. Architecture changes only if you still have headroom problems after 1–10.

Do them in this order. Each step makes the next step easier to measure.

If you’d rather have this done properly by experts, our WordPress Speed & Performance Optimization service starts at $499 and includes a full Core Web Vitals audit, a query-level review, and the exact wc_product_meta_lookup + index work described above. For stores over 20,000 products we also offer a dedicated large-catalog optimization package — reach out and we’ll scope it.