When you scale WooCommerce to 100K+ products, the default setup stops being enough. Running a WooCommerce store with ten thousand products is a performance challenge. Running one with a hundred thousand is an architecture problem. The default WooCommerce setup starts showing strain somewhere around twenty thousand SKUs, and by the time the catalog crosses the six-figure mark most stores need a rebuild of the product pipeline, search layer, and admin workflow. This guide is the playbook we use on real client stores that carry 100K+ products without turning the storefront into a loading spinner.
If you are already past fifty thousand products and your category pages take more than three seconds to paint, the fixes below are the ones that move the needle. We will go through product indexing, Elasticsearch integration, lazy loading for catalog pages, pagination strategy, admin performance, bulk import optimization, and the server architecture that ties it all together. Every section includes production code or exact configuration rather than vague advice.
- Product indexing with a denormalized table for fast catalog reads
- Elasticsearch for search, faceted filters, and autocomplete
- Lazy loading catalog pages with Intersection Observer
- Keyset pagination for deep category pages
- Admin performance fixes that unblock your team
- Bulk import optimization for ERP and supplier syncs
- Server architecture that holds up at 100K products
Why Stock WooCommerce Breaks When You Scale WooCommerce to 100K Products
WooCommerce stores product data across four main tables: wp_posts, wp_postmeta, wp_terms, and wp_term_relationships. A single product with variations, custom fields, and category assignments can produce fifty to two hundred rows across those tables. Multiply that by 100,000 products and you have tens of millions of wp_postmeta rows in a single table that WordPress was never designed to query fast.
The three hotspots that fail first are category archives, internal search, and the admin product list. Category archives run a WP_Query with taxonomy joins and meta filters, which MySQL resolves with temporary tables once the dataset gets large. Internal search uses LIKE '%term%' against wp_posts.post_title and wp_posts.post_content, which cannot use an index and forces a full table scan. The admin product list loads the full product object for every row, pulls all variations, and calls stock sync on every page view.
Caching delays the pain. It does not solve it. Once you cross fifty thousand products, you need a dedicated search index, a denormalized product feed, and a server setup that separates read and write paths.
Step 1: Product Indexing with a Denormalized Table
The single biggest win when you scale WooCommerce to 100K products is moving product read queries off wp_postmeta onto a denormalized product index table. This is the pattern used by every large WooCommerce site I have worked on, and it is the same approach WooCommerce itself adopted for orders through the High-Performance Order Storage migration.
The idea is simple. Create a flat table where each row is one product with the columns you actually filter and sort on: price, stock status, rating, category IDs as a packed field, date created, and whatever custom attributes your store uses. Keep that table in sync with the canonical WooCommerce data through hooks, and point your catalog queries at it.
CREATE TABLE wp_wc_product_index (
product_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
sku VARCHAR(100) NULL,
price DECIMAL(19,4) NULL,
sale_price DECIMAL(19,4) NULL,
stock_status VARCHAR(20) NOT NULL DEFAULT 'instock',
stock_quantity INT NULL,
average_rating DECIMAL(3,2) NOT NULL DEFAULT 0,
review_count INT NOT NULL DEFAULT 0,
total_sales BIGINT NOT NULL DEFAULT 0,
category_ids VARCHAR(500) NOT NULL DEFAULT '',
brand_id INT NULL,
featured TINYINT(1) NOT NULL DEFAULT 0,
on_sale TINYINT(1) NOT NULL DEFAULT 0,
visibility VARCHAR(20) NOT NULL DEFAULT 'visible',
date_created DATETIME NULL,
date_modified DATETIME NULL,
INDEX idx_price (price),
INDEX idx_stock (stock_status, visibility),
INDEX idx_category (category_ids(100)),
INDEX idx_featured_sale (featured, on_sale),
INDEX idx_date (date_created),
INDEX idx_sales (total_sales)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;Keep it in sync with a save hook on woocommerce_update_product and woocommerce_new_product. For the initial backfill, use a WP-CLI command that chunks through products five hundred at a time. The key rule: this table is read-only from the storefront and write-only from WooCommerce hooks. Never let your template code modify it directly.
add_action( 'woocommerce_update_product', 'wcd_sync_product_index', 20, 2 );
add_action( 'woocommerce_new_product', 'wcd_sync_product_index', 20, 2 );
function wcd_sync_product_index( $product_id, $product ) {
global $wpdb;
if ( ! $product instanceof WC_Product ) {
$product = wc_get_product( $product_id );
}
if ( ! $product ) {
return;
}
$category_ids = wp_list_pluck( get_the_terms( $product_id, 'product_cat' ) ?: [], 'term_id' );
$wpdb->replace(
"{$wpdb->prefix}wc_product_index",
[
'product_id' => $product_id,
'sku' => $product->get_sku(),
'price' => $product->get_price(),
'sale_price' => $product->get_sale_price(),
'stock_status' => $product->get_stock_status(),
'stock_quantity' => $product->get_stock_quantity(),
'average_rating' => $product->get_average_rating(),
'review_count' => $product->get_review_count(),
'total_sales' => (int) $product->get_total_sales(),
'category_ids' => ',' . implode( ',', $category_ids ) . ',',
'featured' => $product->is_featured() ? 1 : 0,
'on_sale' => $product->is_on_sale() ? 1 : 0,
'visibility' => $product->get_catalog_visibility(),
'date_created' => $product->get_date_created() ? $product->get_date_created()->date( 'Y-m-d H:i:s' ) : null,
'date_modified' => current_time( 'mysql' ),
]
);
}The category_ids trick with leading and trailing commas is deliberate. It lets you filter by category using WHERE category_ids LIKE '%,15,%' which MySQL can resolve against the index prefix. For more advanced queries, split categories into a join table, but for stores up to three hundred thousand products the packed field is fast enough and drastically simpler to maintain.
Step 2: Elasticsearch for Search and Faceted Filters
Once the product index is running, internal search is the next bottleneck. Native WooCommerce search cannot rank results, does not handle typos, and does not support faceted filters without another layer of pain. For stores past twenty thousand products, Elasticsearch or OpenSearch is the right answer.
We use Elasticsearch with the ElasticPress plugin as a starting point, then override the indexing pipeline to include our denormalized fields. The reason we do not use ElasticPress defaults on large stores is that ElasticPress re-reads product data from WooCommerce on every index rebuild, which is slow and re-hits the meta table. Instead, we feed Elasticsearch directly from wp_wc_product_index, which cuts index rebuild time from eight hours to forty minutes on a 120K product store.
The Elasticsearch mapping we use for product search looks like this:
{
"settings": {
"number_of_shards": 2,
"number_of_replicas": 1,
"analysis": {
"analyzer": {
"product_name": {
"type": "custom",
"tokenizer": "standard",
"filter": ["lowercase", "asciifolding", "product_synonyms", "edge_ngram_filter"]
}
},
"filter": {
"edge_ngram_filter": {
"type": "edge_ngram",
"min_gram": 2,
"max_gram": 15
},
"product_synonyms": {
"type": "synonym_graph",
"synonyms_path": "analysis/product_synonyms.txt"
}
}
}
},
"mappings": {
"properties": {
"name": { "type": "text", "analyzer": "product_name", "search_analyzer": "standard" },
"sku": { "type": "keyword" },
"description": { "type": "text" },
"price": { "type": "scaled_float", "scaling_factor": 100 },
"stock_status": { "type": "keyword" },
"categories": { "type": "keyword" },
"brand": { "type": "keyword" },
"attributes": { "type": "flattened" },
"total_sales": { "type": "long" },
"rating": { "type": "half_float" },
"featured": { "type": "boolean" },
"on_sale": { "type": "boolean" }
}
}
}Two things matter here. First, the edge_ngram analyzer on product names gives you autocomplete that feels instant and tolerates partial typing. Second, the flattened type for attributes lets you add custom product attributes without reindexing the full mapping. That matters because large stores add attributes constantly, and full reindexes on a million-document index take hours.
Queries from the storefront should hit Elasticsearch directly, not through WooCommerce. Use pre_get_posts to short-circuit the main query on shop and search templates, fetch IDs from Elasticsearch, then run a single posts__in WP_Query to load the display data. This way you get Elasticsearch speed with the WordPress template ecosystem intact.
Step 3: Lazy Loading Catalogs the Right Way
Catalog pages on large stores routinely load forty to a hundred products on first paint. Even with a fast backend, shipping that many product cards blocks the critical rendering path. Lazy loading fixes this, but only if it is done with real browser APIs rather than jQuery legacy code.
Use the Intersection Observer API for product images and a server-rendered pagination approach for product cards below the fold. Never use JavaScript-only infinite scroll on catalog pages, because it destroys SEO and breaks the browser back button. The pattern that works is: render the first twelve to twenty products server-side, then use Intersection Observer to trigger fetching the next page as the user scrolls into view.
const catalog = document.querySelector('.products-grid');
const sentinel = document.querySelector('.catalog-sentinel');
let nextPage = 2;
let loading = false;
let exhausted = false;
const observer = new IntersectionObserver(async (entries) => {
if (!entries[0].isIntersecting || loading || exhausted) return;
loading = true;
try {
const url = new URL(window.location.href);
url.searchParams.set('paged', nextPage);
url.searchParams.set('partial', '1');
const response = await fetch(url.toString(), { headers: { 'Accept': 'text/html' } });
if (!response.ok) throw new Error('Fetch failed');
const html = await response.text();
if (!html.trim()) {
exhausted = true;
sentinel.remove();
return;
}
catalog.insertAdjacentHTML('beforeend', html);
history.replaceState(null, '', url.toString());
nextPage++;
} catch (e) {
console.warn('Catalog pagination failed', e);
exhausted = true;
} finally {
loading = false;
}
}, { rootMargin: '400px 0px' });
observer.observe(sentinel);On the server, add a partial query parameter handler that returns only the product card HTML for the requested page. Keep the pagination accessible by rendering real anchor links in a <nav> that progressive enhancement hides, so search engines and assistive tech still see every page. The 400px root margin in the observer pre-fetches the next batch before the user reaches the sentinel, which eliminates the visible loading pause on good connections.
For product images inside cards, use native loading="lazy" and the fetchpriority attribute. Set fetchpriority="high" on the first four to six cards that will be in the initial viewport and leave the rest on lazy. This simple combination shaves eight hundred milliseconds off LCP on mobile for most large catalogs.
Step 4: Category Pagination That Scales to Thousands of Pages
A 100K product store with twenty products per category page has five thousand pages per top-level category. Most WooCommerce themes still use paginate_links() with numeric pagination, which becomes unusable once you hit three digits of page numbers. More importantly, deep pagination with OFFSET is catastrophically slow on MySQL because every page load scans and discards all prior rows before returning the current window.
The fix is keyset pagination. Instead of LIMIT 20 OFFSET 9980, pass the last product ID and price from the previous page and use them as the starting point. MySQL can resolve this with a direct index seek, which runs in constant time regardless of how deep into the catalog the user is.
SELECT product_id, price
FROM wp_wc_product_index
WHERE category_ids LIKE '%,15,%'
AND stock_status = 'instock'
AND (price, product_id) < (49.99, 245601)
ORDER BY price DESC, product_id DESC
LIMIT 20;The tuple comparison (price, product_id) < (49.99, 245601) gives you a stable sort that does not drift when new products are added mid-browse. Combine keyset pagination with a hybrid UI that shows “Previous / Next” buttons on deep pages and numeric pagination on the first ten pages. Users almost never jump to page two hundred by choice, so optimize for the realistic browsing pattern.
For category pages themselves, generate sitemap entries for the first twenty pages and let rel="canonical" point deeper pages at the filtered URL. This keeps crawl budget sane and avoids indexing five thousand near-duplicate pages per category.
Step 5: Admin Performance for Large Catalogs
The WooCommerce admin product list is often more painful than the storefront on large catalogs. The default list table loads the full WC_Product object for every row, queries variations, and runs stock sync on every page load. On 100K products, the admin list can take fifteen to thirty seconds to render. Nobody on the team will stop complaining about this until you fix it.
There are three levers to pull:
- Disable the count queries that populate the status filters at the top of the product list.
- Override the admin list query to read from your product index table instead of the heavy WooCommerce admin query.
- Disable the stock and variation sync calls that run during admin list rendering.
On a million-post database, COUNT(*) queries take multiple seconds each and run on every admin page load. Short-circuit them like this:
add_filter( 'wp_count_posts', 'wcd_short_circuit_product_counts', 10, 3 );
function wcd_short_circuit_product_counts( $counts, $type, $perm ) {
if ( $type !== 'product' || ! is_admin() ) {
return $counts;
}
$cached = get_transient( 'wcd_product_counts' );
if ( $cached !== false ) {
return $cached;
}
global $wpdb;
$rows = $wpdb->get_results(
"SELECT post_status, COUNT(*) AS num_posts
FROM {$wpdb->posts}
WHERE post_type = 'product'
GROUP BY post_status",
OBJECT_K
);
$result = new stdClass();
foreach ( [ 'publish', 'draft', 'trash', 'pending', 'private', 'future' ] as $status ) {
$result->$status = isset( $rows[ $status ] ) ? (int) $rows[ $status ]->num_posts : 0;
}
set_transient( 'wcd_product_counts', $result, 5 * MINUTE_IN_SECONDS );
return $result;
}For the second lever, use the pre_get_posts hook on edit-product screen to inject a cheaper query that returns product IDs, then let the default list table fetch minimum data only when building rows. For the third lever, the database optimization guide covers the specific hooks to unhook, along with the broader Action Scheduler cleanup that every large store needs.
Step 6: Bulk Import Optimization
Clients with large catalogs import from ERP systems, supplier feeds, or manufacturer APIs. The default WooCommerce CSV import runs single inserts, fires hooks on every product, and rebuilds the product index on every save. A 50K product import can take eight to twelve hours this way. Done correctly, the same import takes twenty to forty minutes.
The pattern we use has four phases:
- Disable non-critical hooks for the duration of the import using
remove_all_actions()on specific WooCommerce hooks, then restore them after. - Use
wpdb->query()with batched INSERT statements rather thanwp_insert_post(). - Write to a staging table first, then swap into the live
wp_poststable in a single atomic operation. - Queue index rebuilds through Action Scheduler rather than running them inline.
function wcd_bulk_import_products( $products, $chunk_size = 500 ) {
global $wpdb;
wcd_suspend_expensive_hooks();
$wpdb->query( 'SET autocommit=0' );
$wpdb->query( 'START TRANSACTION' );
try {
foreach ( array_chunk( $products, $chunk_size ) as $chunk ) {
$values = [];
$placeholders = [];
foreach ( $chunk as $p ) {
$placeholders[] = '(%s, %s, %s, %s, %s, %s, %s)';
array_push(
$values,
$p['title'],
$p['content'],
$p['excerpt'],
'product',
'publish',
current_time( 'mysql' ),
current_time( 'mysql' )
);
}
$sql = "INSERT INTO {$wpdb->posts}
(post_title, post_content, post_excerpt, post_type, post_status, post_date, post_modified)
VALUES " . implode( ', ', $placeholders );
$wpdb->query( $wpdb->prepare( $sql, $values ) );
}
$wpdb->query( 'COMMIT' );
as_enqueue_async_action( 'wcd_rebuild_product_index_after_import', [], 'wcd-imports' );
} catch ( Exception $e ) {
$wpdb->query( 'ROLLBACK' );
throw $e;
} finally {
$wpdb->query( 'SET autocommit=1' );
wcd_restore_expensive_hooks();
}
}The chunk size of 500 keeps the MySQL packet under max_allowed_packet for most hosts. The transaction wrapper means a failure halfway through does not leave partial products in the database. And the Action Scheduler callback at the end handles all the index rebuilds, postmeta inserts, and taxonomy assignments asynchronously, which lets the import return fast and the heavy work happen in background workers.
For ongoing syncs from an ERP, run the import through WP-CLI rather than admin-ajax. A long running WP-CLI command has no PHP execution timeout, no memory limit issues from Apache, and gives you clean stdout for monitoring. Schedule it with system cron, never with wp-cron, because wp-cron stops firing reliably once the site gets busy. The high-traffic sales events guide covers the same queue and worker pattern from a different angle.
Step 7: Server Architecture That Supports 100K Products
You cannot fix 100K product performance inside WordPress alone. The server architecture has to support it. After building and operating dozens of these stores, here is the minimum viable architecture that consistently holds up under real traffic.
- Dedicated MySQL 8.0 with 16GB+ RAM,
innodb_buffer_pool_sizeat 70% of memory - Read replicas via HyperDB or LudicrousDB for storefront reads
- Redis object cache at 4GB minimum for a 100K product store
- CDN with cart-aware rules in front of everything
- PHP 8.2 or 8.3 with JIT and OPcache preload of WooCommerce core
Start with a dedicated database server. Shared MySQL on a bundled hosting plan will never handle the query load of a large WooCommerce catalog, no matter how well the code is written. We provision MySQL 8.0 on a separate host with at least 16GB RAM, innodb_buffer_pool_size set to 70% of system memory, and innodb_flush_log_at_trx_commit tuned to 2 for storefront reads.
Read replicas give you horizontal read scaling and a disaster recovery target. WordPress has good plugin support for read replicas through HyperDB and LudicrousDB, and our approach is to route all storefront SELECT queries to the replicas while keeping admin and checkout on the primary.
Object caching goes to Redis, not Memcached. Redis supports eviction policies that make sense for large caches, survives restarts without losing everything, and integrates cleanly with WooCommerce through the Redis Object Cache plugin. Size the Redis instance at 4GB minimum for a 100K product store, because the object cache fills up with product meta, term relationships, and order data quickly.
Put a CDN in front of everything with cache rules that respect cart and checkout. The guide on configuring a CDN for WooCommerce covers the Cloudflare ruleset we use to keep dynamic pages uncached while aggressively caching static assets and catalog pages. On top of that, run page caching with cart-aware exclusions at the origin so CDN misses still return fast.
Finally, run PHP 8.2 or 8.3 with OPcache enabled and opcache.jit=tracing. PHP 8.x with JIT is thirty to forty percent faster than PHP 7.4 on WooCommerce workloads. Do not skip the OPcache preload configuration either. Preloading the WooCommerce core classes at boot time saves eighty to a hundred milliseconds off every page request.
Monitoring: What to Watch After Deployment
Large WooCommerce stores fail quietly. The site looks fine on the home page, the cart adds products, the checkout goes through. But buried in the logs are slow queries, failed Action Scheduler jobs, and Redis evictions that add up to a gradual degradation of the customer experience. You need monitoring in place from day one.
- p95 response time on category pages (alert above 2 seconds)
- Action Scheduler queue depth (alert above 10,000 pending)
- Redis hit rate (alert below 90%)
- MySQL slow queries per hour (alert above 100)
New Relic, Blackfire, or Query Monitor in production mode all work for this. Whichever tool you pick, put dashboards on the wall and review them weekly. Most scale incidents on large stores are preceded by two weeks of creeping metrics that nobody looked at.
When You Need Custom Architecture Work
Everything in this guide works for catalogs up to around three hundred thousand products on commodity hardware with the right tuning. Past that, you start looking at harder problems: sharding the product database, splitting catalog and checkout into separate WordPress installations, or moving to a headless architecture with WooCommerce as a backend service only. Those are architectural decisions that depend on your team, your traffic pattern, and your growth trajectory, and they are not one-size-fits-all.
If you are running a large WooCommerce catalog and hitting any of the ceilings described above, this is exactly the kind of work our team specializes in. We build the product index layer, configure Elasticsearch, tune the server architecture, and leave behind documentation that your team can maintain. Reach out through the contact form and we can walk through your specific bottlenecks.
Wrap Up: Scale WooCommerce to 100K Products, the Repeatable Way
To scale WooCommerce to 100K products without losing site speed, seven things have to land at the same time. Denormalize your product data into an index table. Move search to Elasticsearch. Lazy load catalog pages with real browser APIs. Use keyset pagination for deep categories. Cut admin overhead aggressively. Import in batches through WP-CLI. And build a server architecture with dedicated MySQL, read replicas, Redis, and a proper CDN.
Do the seven steps above and your store handles the catalog size without buckling, the admin team stops complaining about slow pages, and your customers get the instant experience they already expect from the rest of the web. The work is real, but the playbook is repeatable, and every piece compounds on the others.
