Why WooCommerce Slows Down After 10,000 Products

If your WooCommerce store got noticeably slower somewhere between five and fifteen thousand products, the cause is almost certainly your database, not your hosting plan. Product count changes the shape of the work MySQL has to do on every catalog page, every filter click, every search, and every admin screen. More CPU does not change that shape. It just pays for the same bad query a little faster.

The three things that fix it, in the order they pay off: make sure WooCommerce’s lookup tables are actually populated, stop the catalog queries that scan the whole product table (out-of-stock hiding and attribute filters are the usual culprits), and put a persistent object cache in front of the queries that page caching never sees. Below is what each of those means, why catalog size specifically triggers them, and how to tell which one is biting you.

Why product count breaks things but traffic doesn’t

WordPress stores products in a structure borrowed from blog posts. The product itself is one row in wp_posts. Everything that makes it a product — price, SKU, stock quantity, stock status, weight, dimensions, sale dates, backorder rules, download settings, plus whatever your theme and plugins bolt on — is stored as separate key-value rows in wp_postmeta.

That design is called EAV (entity-attribute-value), and it is fine at blog scale. At catalog scale the arithmetic turns against you. A simple product typically carries somewhere around 25 to 30 meta rows once a theme and a couple of plugins have had their say. Variations each carry their own set. So:

Those are multiplications, not benchmarks — the point is the slope. A query that filters or sorts on product meta has to join a table with millions of rows to a table with tens of thousands, and it has to do that per request. Traffic multiplies how often the query runs. Catalog size multiplies how expensive each run is. That is why a store can survive a traffic spike fine and then fall over after a bulk product import.

WooCommerce has known this for years and its answer is lookup tables: flat, indexed tables that hold the handful of fields catalog pages actually query, so the postmeta join is avoided. If yours are empty or stale, you are running a 2018-era store on 2026 code.

Check your lookup tables first

There are two of them, and they exist precisely so that large catalogs stay usable.

wc_product_meta_lookup, added in WooCommerce 3.6, holds one row per product with the columns catalog pages sort and filter on: product_id, sku, virtual, downloadable, min_price, max_price, onsale, stock_quantity, stock_status, rating_count, average_rating and total_sales. WooCommerce’s own testing at the time, on a dataset of 25,000 products and 50,000 variations, measured frontend requests up to 62% faster between 3.5 and 3.6, and backend SKU search dropping from roughly 40 seconds to under one second. Stores between 20 and 1,000 products saw almost no difference — which is exactly the point about scale.

wc_product_attributes_lookup handles filtering by attributes (size, colour, brand) without the term-relationship joins that made faceted navigation so slow historically.

Both are maintained incrementally as products change, and both drift out of sync — most often after a bulk import, a migration, a direct SQL update, or a plugin that writes product meta without going through the WooCommerce CRUD layer. When they drift, WooCommerce quietly falls back to the slow path or, worse, returns wrong counts in filters.

Rebuild them from WP-CLI rather than clicking through the admin, which times out on large catalogs:

wp wc update             # run pending WooCommerce DB updates first
wp wc cot verify_cot_data --log  # HPOS data check, if applicable

# Attribute lookup table
wp wc palt regenerate --yes

# Product meta lookup: check for gaps before rebuilding
wp db query "SELECT COUNT(*) FROM wp_posts WHERE post_type='product' AND post_status='publish';"
wp db query "SELECT COUNT(*) FROM wp_wc_product_meta_lookup;"

If those two counts are meaningfully different, the lookup table is incomplete and that alone can account for most of the slowdown. Regenerating it is available through Tools in the admin, and through the community Regenerate Product Lookup Table plugin for stores where the built-in scheduler has stalled.

One thing worth knowing if you are on WooCommerce 9.1 or later: there is an opt-in “Optimized updates” setting for the attributes lookup table that replaces per-product object loading with direct queries, and raises the background regeneration batch size from 10 products to 100. WooCommerce’s own note is that it bypasses standard functions and may not be compatible with some extensions. On a heavily extended store, enable it on staging and check that attribute filters still return the right products before you enable it in production.

The queries page caching never sees

This is where most store owners get stuck. They install a caching plugin, the homepage scores well, and the store still feels slow — because the pages that matter to a shopper with a large catalog are precisely the ones that cannot be served from a full-page cache.

Faceted filtering is the worst case. Five attributes with four values each is over a thousand possible URL combinations before you add price ranges and sorting — a cache hit rate close to zero, with every miss running the expensive query. The same logic applies to your own team: the admin product list is uncacheable by definition, which is why staff often notice the problem months before customers complain about it.

The fix for this category is a persistent object cache — Redis or Memcached — which caches the results of queries rather than finished pages, and therefore helps logged-in users, filtered views and admin screens alike. If your host offers it, turn it on. If it does not, that is a genuine reason to move, and roughly the only one on this list. We covered the same distinction from the speed side in why WordPress sites take 8 seconds to load.

Stop the settings that scan the whole catalog

Two defaults quietly force a full-catalog query on every archive page.

“Hide out of stock items from the catalog” (WooCommerce → Settings → Products → Inventory) adds a meta condition to every product query. On a small store it is free. On a catalog of tens of thousands it means filtering the whole set on a meta value on every page load. If you need this behaviour, make sure wc_product_meta_lookup is populated so the stock_status column does the work; if you do not strictly need it, turning it off is a one-click improvement.

Layered-nav and filter widgets from older plugins often build their own queries with NOT IN subqueries against postmeta and term relationships, ignoring the attributes lookup table entirely. If your filters were installed before 2022 and never revisited, check whether the plugin supports the lookup table. Many filter plugins now do; the ones that do not are a straight swap.

Also worth checking on product pages: variable products switch from static dropdowns to AJAX-loaded variations above a threshold set by the woocommerce_ajax_variation_threshold filter, which defaults to 30. Raising it — a common “fix” copied from forums — pushes every variation’s data into the initial page load. WooCommerce’s own documentation advises choosing the lowest value that works, because higher values hurt product-page performance.

If your catalog is large and you cannot tell which of these is responsible, that is a measurement problem rather than a guessing problem — a query log under real traffic answers it in an afternoon. Tell us what your store looks like and we will tell you which of these is costing you the most before you change anything.

Autoloaded options and transient bloat

Every WordPress request loads the autoloaded rows of wp_options into memory before anything else happens. Large stores accumulate these: plugin settings blobs, cached attribute lists, stray transients that never got a cleanup routine.

WordPress 6.6 changed the defaults here. Options larger than 150,000 bytes are no longer set to autoload by default when no explicit value is passed, and Site Health now flags a critical issue when the total autoloaded size exceeds 800 KB. The relevant filters, if you need to tune the behaviour, are wp_default_autoload_value, wp_max_autoloaded_option_size and wp_autoload_values_to_autoload.

Check where you stand:

wp db query "SELECT ROUND(SUM(LENGTH(option_value))/1024) AS autoload_kb
  FROM wp_options WHERE autoload IN ('yes','on','auto-on');"

wp db query "SELECT option_name, ROUND(LENGTH(option_value)/1024) AS kb
  FROM wp_options WHERE autoload IN ('yes','on','auto-on')
  ORDER BY LENGTH(option_value) DESC LIMIT 20;"

wp transient delete --expired

Anything above the 800 KB total is worth investigating. Individual options in the hundreds of kilobytes are almost always a plugin caching something it should not be autoloading.

Orders: HPOS, which is a separate problem

Large catalogs and large order histories degrade for related but distinct reasons. High-Performance Order Storage moves orders out of wp_posts/wp_postmeta into four purpose-built tables — wc_orders, wc_order_addresses, wc_order_operational_data and wc_orders_meta. It has been the default for new installations since WooCommerce 8.2, released in October 2023.

Stores that existed before then are frequently still on legacy storage, or — more commonly and more expensively — running with compatibility mode enabled, which writes every order to both structures. That was intended as a migration safety net, not a permanent state. If your admin order list is slow, check which mode you are in under WooCommerce → Settings → Advanced → Features before you look anywhere else.

Migrating is straightforward in principle and occasionally disruptive in practice, because any extension that queries orders with WP_Query or reads order postmeta directly will break. Test on staging, confirm your extensions declare HPOS compatibility, and keep compatibility mode on only for as long as the verification takes.

When the real answer is a search index

Past roughly 50,000 products, or with heavy faceted navigation on a smaller catalog, MySQL stops being the right tool for catalog browsing regardless of how well you tune it. Native WordPress search uses LIKE matching against post content, which cannot use an index efficiently and returns poor relevance on top of being slow.

At that point the answer is to move search and filtering to Elasticsearch or a hosted equivalent, typically via ElasticPress or a dedicated product-search service. This is a real project with real cost, not a plugin install, so it is worth being honest about the trade-off: you gain fast, relevant, facet-friendly search and you take on a second system to keep in sync and pay for. Below that scale, the lookup tables and an object cache usually get you where you need to be.

Do them in this order

Worth noting what none of this fixes: a theme that loads 2 MB of JavaScript, unoptimised product images, or a page builder rendering every category tile through a nested shortcode. Those hurt every store equally and do not get worse with catalog size — which is how you tell them apart from the problems above. If your store is slow at 500 products too, start there instead.

For context on the platform trade-offs generally, see why we build on WooCommerce and what a heavily customised WooCommerce build looks like in practice. If you are still deciding whether to invest in the store at all, this covers the commercial case.

Getting a straight answer about your own store

The hard part of a large-catalog slowdown is not fixing it. It is knowing which of six plausible causes is actually responsible, because they produce the same symptom and the wrong fix costs weeks. A database and query audit — slow query log under production traffic, lookup table integrity, autoload size, object cache hit rate, HPOS state — answers that in a day or two and tells you whether you are looking at an afternoon’s work or a migration.

If your catalog has grown past the point where the store feels comfortable, send us the details — product and variation counts, host, and which pages feel worst — and we will tell you what we would check first and what it would take to fix.