WooCommerce past 40,000 products

A store at 40,000 products is not a database problem. That is the single most useful thing to know before spending money on one, because the brief almost always arrives describing a database problem.

What it usually turns out to be is an inventory of decisions: a dozen plugins installed to solve small problems, each adding a join to every product query, none of them ever removed. The work that makes a large catalogue fast is mostly deleting things, which is why it is cheaper than a replatform and why nobody enjoys selling it.

Why large stores get slow

Not the product count. WooCommerce stores products as posts, and MySQL is perfectly happy with 40,000 rows in wp_posts. A million-row wp_postmeta table is also fine when it is indexed properly. If somebody tells you 40,000 products is too many for WooCommerce, they have diagnosed the wrong thing.

What actually happens is this.

Every plugin adds a join. A filtered category page starts as one query. Add a filter plugin, a badge plugin, a pricing rule plugin and a stock plugin, and each hooks posts_clauses to add its own meta join. Now it is a six-way self-join on wp_postmeta and the query planner has given up. The individual plugins are not badly written. The composition is what kills you, and no individual author is responsible for it.

wp_options autoload becomes enormous. Every plugin storing settings, transients or a cached blob with autoload = yes adds to a payload loaded on every single request, before any of your code runs. Several megabytes of autoloaded options is not unusual on an old store, and it produces a site that feels slow everywhere with no single slow query to point at. It is the most commonly missed cause of site-wide slowness and one of the easiest to fix.

Cart fragments run on every page. The default ?wc-ajax=get_refreshed_fragments call is uncached, hits PHP, and fires on pages with no cart on them. On a slow origin that is a second added to every page a customer sees, including the blog and the contact page.

Variations multiply everything. 40,000 products with four variations each is 200,000 posts, not 40,000. Most “we have 40,000 products” stores actually carry several hundred thousand rows once variations are counted, and every price query touches them. When you are estimating whether a catalogue is large, count variations.

Nothing is cached at the object layer. Without a persistent object cache every request re-runs the same option lookups, term queries and meta reads that have not changed in a week. This is free to fix and frequently absent.

HPOS first

High-Performance Order Storage moves orders out of wp_posts and wp_postmeta into dedicated tables with real columns and real indexes. If you run a store of any size on a modern WooCommerce and have not enabled it, start there. It is the highest ratio of benefit to effort available on this list.

What it changes: order queries stop competing with product queries for the same tables. Admin order search goes from seconds to instant. Reporting stops locking things that customers are trying to read.

What to watch:

• Run in sync mode first. WooCommerce writes to both the legacy tables and the new ones while you verify. Do not switch off the legacy write until reconciliation is clean, and give it at least a fortnight of real trading including a month boundary.
• Every order-touching plugin must be HPOS-compatible. Ones querying wp_postmeta for order data directly will silently return nothing, which means a fulfilment integration that appears to work and quietly ships no orders. Check the compatibility declaration before you enable, not after.
• Custom code reading order meta needs updating to the CRUD API. Direct get_post_meta() on an order id stops being correct. Grep for it across the theme and any custom plugins before you start.
• The sync itself takes time on a large store. Plan it, run it off-peak, and watch the queue rather than assuming it finished.

On a large store, HPOS plus a plugin cull is usually the difference between an admin order search that takes several seconds and one that returns immediately. Staff notice that before customers notice anything, and staff time is a cost people routinely forget to count.

The plugin audit, which is most of the work

On most large stores the plugin audit is a bigger performance win than any database change, and it is the first thing we do rather than the last.

How we run it:

1. Query Monitor on a staging clone with production data. Not a fresh install. The plugin behaviour that matters only shows up at real volume, and a plugin that adds one query on a demo store adds one query per product on yours.
2. Bisect. Disable half, measure, disable half of what remains. Crude, and it finds the culprit faster than reading code. Half a day of bisecting beats two days of reading.
3. For each plugin, ask what it costs per request. A plugin adding 40ms to every page is a plugin costing you conversions. Write the number next to the name, then take the list to whoever asked for the plugin.
4. Check the last release date, not the star rating. An abandoned plugin on a store is a security problem with a countdown attached, and star ratings are a record of how it worked three years ago.

The categories that keep appearing

Across audits, the offenders sort into four groups:

• Filter and faceted search plugins. The worst performers by a distance, because they hook every product query by design. A dedicated search index usually replaces them outright and is faster.
• Pricing and discount rule engines. These recalculate on every product view. Precomputing prices on save rather than on read is nearly always possible and nearly always what we do.
• Anything that adds a badge, label or ribbon. Individually trivial, collectively a meta lookup per product per page.
• Analytics and marketing tags installed as plugins. Each one adds PHP to the request to output JavaScript that a tag manager could have delivered.

The two that most often need rewriting rather than removing are custom pricing rules and a fulfilment integration, because the business genuinely needs both and the off-the-shelf versions tend to run on every product query. Moved to precompute on save and cache, they cost nothing per request. That is the general pattern: the requirement is usually legitimate and the implementation is usually in the wrong place.

Indexes worth adding

WooCommerce ships reasonable indexes. Large catalogues usually need a few more, and the right ones depend on your queries. Check with EXPLAIN before adding anything.

The workflow: capture the actual slow query from Query Monitor or the slow query log, run EXPLAIN on it, and look for type: ALL (a full table scan) or a rows count in the hundreds of thousands. Add the index, re-run, confirm the plan changed. If the plan did not change, the index was not the problem and you have just slowed your writes down for nothing.

The ones we add most often:

• A composite on wp_postmeta (meta_key, meta_value(20), post_id) for the specific meta keys that get filtered on. Prefix length matters, since indexing a full longtext is wasteful and MySQL has a key length limit you will hit.
• wp_wc_product_meta_lookup already exists and is already indexed. Make sure your filters actually use it rather than going to wp_postmeta. Most badly written filter plugins do not, and pointing them at it is sometimes a one-line filter.
• On HPOS, check that wc_orders (status, date_created_gmt) covers your admin filters. The default indexes cover the common cases and not always the reports your team actually runs.

Do not add indexes speculatively. Every index slows writes, and importing 40,000 products is a lot of writes.

The database maintenance nobody does

Half a day of this on an old store is often worth more than a week of tuning.

Find and fix the autoload payload.

SELECT SUM(LENGTH(option_value)) / 1024 / 1024 AS autoload_mb
FROM wp_options WHERE autoload = 'yes';

SELECT option_name, LENGTH(option_value) / 1024 AS kb
FROM wp_options WHERE autoload = 'yes'
ORDER BY LENGTH(option_value) DESC LIMIT 30;

Anything above 1MB total deserves attention. The list of the thirty largest tells you who is responsible, and it is almost always three or four options from plugins caching things they should have put in a transient.

Clear expired transients. On a neglected store these accumulate into hundreds of thousands of rows, and some of them are autoloaded.

Delete orphaned postmeta. Rows whose parent post no longer exists. Common after bulk product deletions and imports that failed halfway.

Cap post revisions. A product edited weekly for three years carries 150 revisions, each with its own meta. WP_POST_REVISIONS set to a small number stops it recurring.

Empty the action scheduler backlog. WooCommerce’s own scheduler keeps completed actions indefinitely by default. Millions of rows in wp_actionscheduler_actions is normal on an old store and it slows the admin.

Take a backup first, run each on staging, and measure. These are safe operations, but “safe” and “reversible” are not the same word.

The other things that mattered

Object cache, properly. Redis with a persistent object cache. Without it, every request re-runs option and meta queries that never change.

Shipping rates cached. The default behaviour recalculates rates on cart changes, which for table-rate or carrier-API shipping means an external call sitting in the request path. Cache per destination and cart signature. On stores using a live carrier API this is frequently the single slowest thing in the checkout.

Cart fragments removed from pages without a cart. Dequeue it everywhere it is not needed. On mobile that alone is often several hundred milliseconds.

A separate import path. Bulk product imports through the REST API or admin are slow and lock things. Ours run through WP-CLI, in batches, with the object cache flush deferred to the end.

Search moved off the database. Once a catalogue is past a few thousand products, LIKE '%term%' against wp_posts is both slow and bad at its job. Typesense or Meilisearch gives better results and takes the load off entirely.

Imports and catalogue updates at scale

Most large stores have a feed coming in from an ERP or a supplier, and this is where the avoidable outages happen.

• Batch, and keep batches small. Five hundred products at a time with a pause beats one transaction of forty thousand.
• Only write what changed. Comparing a hash of the incoming row against the stored one turns a nightly full import into a few hundred updates. On a catalogue of this size that is typically the difference between a run measured in hours and one measured in minutes.
• Defer indexing and cache flushing to the end, not per product.
• Never import during trading hours if the write volume is significant. Write locks on wp_postmeta are felt by customers immediately.
• Log what the import did, per run, with counts. When prices are wrong on a Monday morning you want to read a log rather than guess.

What to measure, and what actually gets noticed

A typical starting point on a neglected large store: an old PHP version, a double-figure plugin count, no HPOS, no object cache, and a checkout that takes several seconds on mobile. A typical finishing point: current PHP, HPOS on, Redis object cache, cart fragments dequeued, shipping cached, and the two or three plugins that were doing real work rewritten to precompute.

Measure these four, before and after, and insist on field data rather than a lab score:

Two things worth knowing in advance. The number the business cares about is almost never the one engineers quote: checkout milliseconds do not appear in a revenue report and conversion does. And the admin-side improvement is the one the operations team will thank you for, because they have usually been living with it for years without anyone asking.

What we would do in the first two weeks

If you have a large, slow store and a limited budget, this is the order. Most of it is not a rebuild.

1. Measure properly. Real device field data, plus Query Monitor on staging with production data. Two days.
2. Autoload and database maintenance. Half a day, often a visible win.
3. Persistent object cache. Half a day if the host supports Redis.
4. Plugin bisect and cull. Three to four days, the largest single win.
5. HPOS in sync mode. One day to enable, then observation.
6. Dequeue cart fragments and fix the obvious front-end weight. One day.

Two weeks, and on most stores that is the majority of the available improvement. Indexes, search and rewrites come after, once you can see what is left. We price that as a fixed two-day audit at $800 followed by scoped work, because it is not honest to quote the rest before the measurement.

When Woo is the wrong tool

Less often than people say. WooCommerce handles large catalogues fine when it is disciplined. The cases where we would not use it:

• Complex B2B pricing with per-customer contracts and approval workflows. Possible in Woo, painful in practice. That is an application, not a store, and it belongs in a framework with a real schema.
• Marketplaces with many sellers, payouts and commission. The plugins exist, and they are a great deal of surface area to own. Money moving between three parties needs a ledger you can prove, and postmeta is not that.
• Anything where the storefront is not the product. If Woo is only an order API behind a custom front end, ask whether you want WordPress in the stack at all.
• Catalogues past a few hundred thousand variations with heavy faceted search. Achievable, but at that point you are running a search platform beside a commerce platform and should choose deliberately.

If you have a large catalogue and a slow store, the answer is almost never “replatform”. It is an audit, a plugin cull and HPOS. That is two weeks of work before anyone should be discussing a rebuild.

Common questions

How many products can WooCommerce actually handle?

We run 40,000 SKUs comfortably, and we have audited stores at twice that which perform well. There is no hard ceiling. The practical limit is set by variation count, how many plugins touch the product query, and whether anyone maintains the database.

Is HPOS safe to enable on a live store?

Yes, in sync mode, provided you check plugin compatibility first. The risk is not the migration, it is an incompatible plugin reading order data the old way and silently getting nothing. Verify compatibility, run in sync for a fortnight, reconcile, then switch off the legacy write.

Will a better host fix this?

It will help and it will not fix it. Faster PHP and better disks reduce the cost of every unnecessary query without removing any of them. We have moved stores to excellent hosting and gained 20%, then removed four plugins and gained 60%. Do the audit first, then decide what hosting you actually need.

Do we need a headless front end for speed?

Almost certainly not. For a store, full-page caching, a fixed image pipeline and a plugin cull get you most of the available speed at a fraction of the cost and complexity. Decoupling a store also means rebuilding checkout, which is the part you least want to rebuild.

How long does a project like this take?

A straightforward audit and plugin cull is two to three weeks. Add HPOS, a platform upgrade and one or two plugin rewrites and you are into a few months with a small pod. The two-day audit at $800 is what tells you which of those you are looking at, and it is deliberately the cheapest way to find out.