How 288 Product Variations Brought a WooCommerce Admin to Its Knees

The Admin Was Unusable and the Store Owner Was Blaming the Host

A clothing retailer running WooCommerce messaged me: "Editing any product takes forever. The page either loads after 40 seconds or times out completely. My hosting company says the server is fine." The store had around 600 products on a 4-core, 8GB VPS running CloudPanel, PHP 8.3-FPM, Nginx, and MariaDB 10.11. The frontend loaded in under two seconds. The admin was a different story.

I asked which products were worst. "The ones with lots of sizes and colours." That was the clue.

Finding the Worst Offenders

I SSH'd in and checked the PHP-FPM slow log first. Any request taking longer than five seconds gets logged there, and it tells you exactly which PHP function was executing when the timeout hit:

tail -50 /var/log/php/8.3/slow.log

[17-Aug-2026 09:14:22] [pool www] pid 18432
script_filename = /home/client/htdocs/wp-admin/post.php
[0x00007f3a2c014320] execute_query() /home/client/htdocs/wp-content/plugins/woocommerce/src/Internal/DataStores/Orders/DataSynchronizer.php:389
[0x00007f3a2c014120] read_variation() /home/client/htdocs/wp-content/plugins/woocommerce/includes/data-stores/class-wc-product-variation-data-store-cpt.php:112

The slow log was full of read_variation() calls on post.php — the product edit screen. WooCommerce was spending all its time reading variation data from the database.

Next, I checked what the database was actually doing during a product edit load. I opened the MariaDB slow query log:

tail -100 /var/log/mysql/mariadb-slow.log

# Query_time: 2.847  Lock_time: 0.000  Rows_sent: 288  Rows_examined: 847291
SELECT post_id, meta_key, meta_value
FROM wp_postmeta
WHERE post_id IN (12401,12402,12403,...12688)
ORDER BY meta_id ASC;

One query, examining 847,000 rows, returning metadata for 288 variations. That query alone took 2.8 seconds. And it was one of several that fired during a single product edit page load.

Understanding the Scale Problem

Each WooCommerce variation is stored as a separate product_variation post in wp_posts, with its own set of metadata rows in wp_postmeta. A single variation typically has 15–25 meta rows: _price, _regular_price, _sale_price, _sku, _stock, _stock_status, _weight, _length, _width, _height, _manage_stock, _backorders, _tax_class, _tax_status, plus one attribute_* row per attribute.

This store sold clothing with three configurable attributes: size (8 options), colour (12 options), and fabric (3 options). That gives 8 × 12 × 3 = 288 variations per product. Each variation had 22 postmeta rows. One product alone accounted for 288 rows in wp_posts and 6,336 rows in wp_postmeta.

I counted the total:

SELECT COUNT(*) FROM wp_posts WHERE post_type = 'product_variation';

+----------+
| COUNT(*) |
+----------+
|    18720 |
+----------+

Nearly 19,000 variation posts. And the postmeta table:

SELECT COUNT(*) FROM wp_postmeta pm
JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.post_type = 'product_variation';

+----------+
| COUNT(*) |
+----------+
|   411840 |
+----------+

Over 400,000 rows of variation metadata alone. The total wp_postmeta table had 1.2 million rows. On a table that uses the Entity-Attribute-Value pattern with only two useful indexes, queries get expensive fast.

The Admin AJAX Compounding Effect

The product edit screen wasn't just running one heavy query. When you open a variable product in the WooCommerce admin, it loads variations in batches of 10 via sequential AJAX calls to /?wc-ajax=load_variations. For 288 variations, that is 29 AJAX requests fired one after another, each bootstrapping WooCommerce and querying the database.

I watched the network tab in the browser while loading the product edit page:

• Initial page load: 8 seconds (loading the product and its metadata)
• 29 AJAX calls to load_variations: 1–2 seconds each, fired sequentially
• Total time before the page was fully interactive: 42 seconds

Each of those AJAX calls spawned a PHP-FPM worker, ran its queries, rendered the variation HTML, and returned it. During a busy editing session with two staff members updating products simultaneously, the PHP-FPM pool was fully consumed by variation loading requests, making the entire admin sluggish for everyone.

The Fix: Four Changes That Cut Load Time From 42 Seconds to Under 3

1. Add Missing Indexes to wp_postmeta

The default wp_postmeta table has an index on post_id and a separate index on meta_key, but it lacks a composite index that covers the most common WooCommerce query pattern. I added one:

ALTER TABLE wp_postmeta ADD INDEX idx_postid_metakey (post_id, meta_key(191));

This single index change dropped the 2.8-second metadata query to 0.09 seconds. MariaDB could now look up all metadata for a set of variation IDs without scanning the entire table.

I verified with EXPLAIN:

EXPLAIN SELECT post_id, meta_key, meta_value
FROM wp_postmeta
WHERE post_id IN (12401,12402,12403,12404,12405)
ORDER BY meta_id ASC;

Before: type: ALL, rows: 1247891. After: type: range, rows: 110, key: idx_postid_metakey.

2. Increase the Object Cache TTL and Verify Redis Coverage

The store was running Redis object cache, but the variation data was being evicted before it could be reused. Redis was configured with only 64MB — the CloudPanel default — and maxmemory-policy allkeys-lru. On a store this size, 64MB fills up and starts evicting within minutes.

I increased it to 256MB in /etc/redis/redis.conf:

maxmemory 256mb

systemctl restart redis

After the restart, I loaded the product edit screen twice. The second load was noticeably faster because variation objects were served from Redis instead of hitting the database again. This cut repeat loads from 8 seconds to 2 seconds for the initial page render.

3. Reduce Variation Count Where Possible

This is the most impactful fix, but it requires business input. I sat down with the store owner and reviewed the 288-variation products. It turned out that not every size was available in every fabric. The "linen" fabric only came in six sizes, not eight. Several colour-fabric combinations had been discontinued months ago but the variations were still published with zero stock.

I wrote a WP-CLI command to find variations with zero stock that hadn't sold in over six months:

wp db query "
  SELECT v.ID, v.post_title, pm_stock.meta_value AS stock
  FROM wp_posts v
  JOIN wp_postmeta pm_stock ON v.ID = pm_stock.post_id
    AND pm_stock.meta_key = '_stock'
  JOIN wp_postmeta pm_status ON v.ID = pm_status.post_id
    AND pm_status.meta_key = '_stock_status'
  WHERE v.post_type = 'product_variation'
    AND v.post_status = 'publish'
    AND pm_status.meta_value = 'outofstock'
    AND (pm_stock.meta_value = '0' OR pm_stock.meta_value IS NULL)
    AND v.ID NOT IN (
      SELECT DISTINCT oi_meta.meta_value
      FROM wp_woocommerce_order_itemmeta oi_meta
      JOIN wp_woocommerce_order_items oi ON oi_meta.order_item_id = oi.order_item_id
      JOIN wp_posts o ON oi.order_id = o.ID
      WHERE oi_meta.meta_key = '_variation_id'
        AND o.post_date > DATE_SUB(NOW(), INTERVAL 6 MONTH)
    )
" --skip-column-names | wc -l

The result: 4,218 dead variations across the catalogue. We moved them to draft status in bulk:

wp post list --post_type=product_variation --post_status=publish \
  --meta_key=_stock_status --meta_value=outofstock \
  --format=ids | xargs -n 50 wp post update --post_status=draft

After cleaning up, the worst product dropped from 288 variations to 162. The admin edit screen for that product went from 42 seconds to 11 seconds — still slow, but workable.

4. Increase the Variation AJAX Batch Size

WooCommerce loads variations in the admin in batches of 10 by default. For products with 100+ variations, you can increase this to reduce the number of sequential AJAX calls:

add_filter('woocommerce_product_variations_per_page', function () {
    return 50;
});

This dropped the number of AJAX calls from 29 to 4 for a 162-variation product. Combined with the index fix and Redis, the total load time for the product edit screen dropped to 2.8 seconds.

What I Now Monitor on High-Variation Stores

After this job, I added three checks to my maintenance routine for any WooCommerce store with variable products:

1. Variation count per product. I flag any product with more than 100 published variations. Above that threshold, the admin starts to degrade noticeably on typical VPS hardware.

2. Dead variation count. A monthly WP-CLI check for out-of-stock variations with no sales in 90 days. These should be drafted or deleted — they cost database performance for zero commercial value.

3. wp_postmeta table size. Once this table exceeds 500,000 rows, query performance degrades sharply without the composite index. I add the index proactively on every WooCommerce store I onboard.

The deeper lesson: WooCommerce's Entity-Attribute-Value data model in wp_postmeta was designed for blogs with a few custom fields per post. When you use it to store 20+ attributes across thousands of product variations, you are asking a 2003-era data model to do a 2026-era job. The composite index and a well-sized Redis cache paper over the problem. Reducing variation count addresses the root cause. For stores that genuinely need hundreds of variations per product, the long-term answer is HPOS for orders to get order data out of postmeta, and eventually a dedicated product data store — which WooCommerce is actively working on.

Stop Firefighting. Start Maintaining.

I manage 70+ WordPress sites for agencies and businesses. Whether you need ongoing maintenance, emergency support, or a one-off performance fix — I can help.

View Maintenance Plans | Get in Touch