Why a WooCommerce Store Was Showing Wrong Prices — Tracing a Lookup Table Desync After a Bulk Import

The Prices on the Shop Page Didn't Match the Product Pages

A client managing a WooCommerce store with around 1,200 products messaged me on a Monday morning. Their shop page was showing wrong prices for about 40% of their catalogue. Product A showed £29.99 on the shop listing but £34.99 on the actual product page. Several products that were on sale weren't appearing in the "On Sale" filter. And sorting by "Price: low to high" produced results that made no sense — a £150 item sitting between two £12 items.

The product pages themselves were correct. Every individual product showed the right price, the right stock status, the right sale badge. It was only the listings — shop page, category archives, search results, and any filtered view — that were wrong.

The client had done a bulk CSV import the previous Friday to update prices across 500 products for a seasonal promotion. Everything seemed fine at the time. They didn't notice the listing prices were wrong until a customer emailed asking why the checkout price didn't match what they saw on the shop page.

Understanding Where WooCommerce Actually Reads Prices

This is the part that trips people up. WooCommerce stores product data in two places, and it reads from different ones depending on context.

When you load an individual product page, WooCommerce reads from wp_postmeta (or the HPOS orders tables, but product meta is still in postmeta for most stores). This is the canonical source — the actual price, stock status, and SKU live here as _price, _regular_price, _sale_price, _stock_status, and so on.

But when WooCommerce renders a product listing — the shop page, a category archive, a filtered or sorted view — it reads from a denormalised table called wp_wc_product_meta_lookup. This table was introduced in WooCommerce 3.6 as a performance optimisation. Instead of running expensive JOIN queries across wp_posts and wp_postmeta for every product in a listing (which gets slow once you have thousands of products and millions of postmeta rows), WooCommerce maintains a flat lookup table with one row per product:

SELECT product_id, sku, min_price, max_price, onsale,
       stock_quantity, stock_status, average_rating, total_sales
FROM wp_wc_product_meta_lookup
WHERE product_id = 1234;

This is the table that powers price sorting, price filtering, stock status filtering, on-sale queries, and rating-based ordering. If this table is out of sync with the actual postmeta, listings show stale data while product pages show correct data.

Confirming the Desync

I SSH'd into the server and ran a query to compare the lookup table against the canonical postmeta for a sample of the affected products:

SELECT
    p.ID AS product_id,
    p.post_title,
    pm_price.meta_value AS actual_price,
    lookup.min_price AS lookup_price,
    pm_sale.meta_value AS actual_sale_price,
    lookup.onsale AS lookup_onsale
FROM wp_posts p
JOIN wp_postmeta pm_price
    ON p.ID = pm_price.post_id AND pm_price.meta_key = '_price'
JOIN wp_wc_product_meta_lookup lookup
    ON p.ID = lookup.product_id
LEFT JOIN wp_postmeta pm_sale
    ON p.ID = pm_sale.post_id AND pm_sale.meta_key = '_sale_price'
WHERE p.post_type = 'product'
    AND p.post_status = 'publish'
    AND CAST(pm_price.meta_value AS DECIMAL(10,2)) != lookup.min_price
LIMIT 20;

The result confirmed it. Hundreds of products had a mismatch between the actual _price in postmeta and the min_price / max_price in the lookup table. The lookup table was still holding the old prices from before Friday's CSV import.

I counted the total discrepancies:

SELECT COUNT(*) AS desynced_products
FROM wp_posts p
JOIN wp_postmeta pm_price
    ON p.ID = pm_price.post_id AND pm_price.meta_key = '_price'
JOIN wp_wc_product_meta_lookup lookup
    ON p.ID = lookup.product_id
WHERE p.post_type = 'product'
    AND p.post_status = 'publish'
    AND CAST(pm_price.meta_value AS DECIMAL(10,2)) != lookup.min_price;

487 products out of 1,200. Almost exactly the number they'd updated via CSV.

Why CSV Imports Cause This

When you edit a product through the WooCommerce admin, WooCommerce calls WC_Product::save(), which triggers the woocommerce_update_product hook. That hook fires wc_update_product_lookup_tables(), which syncs the lookup table row for that product. The loop is closed.

But not every import method goes through that save path cleanly. The built-in WooCommerce CSV importer does fire the product save hooks, and in most cases it works. But when an import is interrupted — by a PHP timeout, a memory limit, or the browser tab being closed mid-batch — the postmeta gets written but the lookup table sync never completes for the remaining products.

This client's import had 500 products. WooCommerce's CSV importer processes them in batches of 50. The import appeared to complete, but the PHP error log told a different story:

grep -i "fatal\|memory\|timeout" /var/log/php/error.log | grep "$(date -d 'last Friday' +%Y-%m-%d)"

[17-May-2026 14:23:41] PHP Fatal error: Allowed memory size of 268435456 bytes
exhausted (tried to allocate 4194304 bytes) in
/var/www/html/wp-content/plugins/woocommerce/includes/import/class-wc-product-csv-importer.php on line 412

The import had crashed at batch 8 of 10. The CSV importer wrote the raw postmeta for all 500 products (that happens first), but the lookup table sync — which runs as a deferred action after each batch — only completed for the first 350. The remaining 150 products had updated postmeta but stale lookup data.

The other 337 desynced products (487 minus 150) were variable products whose parent lookup rows depend on child variation prices. When a variable product's cheapest variation changes price, the parent's min_price in the lookup table needs recalculating. That recalculation is also deferred and was caught in the same crash.

The Same Problem From External Sources

This exact scenario happens more frequently with external data sources:

• ERP integrations that update _price and _stock directly in wp_postmeta via the database or REST API without calling WC_Product::save()
• Custom import scripts using update_post_meta() instead of the WooCommerce product API
• Bulk editing plugins that write postmeta directly for speed
• Database-level price changes via phpMyAdmin or raw SQL

Any path that updates wp_postmeta without triggering wc_update_product_lookup_tables() will cause a desync. I see this at least once a month across the sites I manage, almost always after a bulk operation.

Fixing It: Regenerating the Lookup Tables

The immediate fix is straightforward. WooCommerce has a built-in tool to regenerate the lookup table.

Via the admin UI:

Navigate to WooCommerce → Status → Tools. Find "Regenerate product lookup tables" and click the button. On a store with 1,200 products, this takes 30-60 seconds.

Via WP-CLI (faster, no timeout risk):

wp wc tool run regenerate_product_lookup_tables --user=1

This truncates wp_wc_product_meta_lookup and rebuilds it from the canonical postmeta data. Every row gets a fresh sync.

For the attribute lookup table (which powers attribute-based filtering), there's a separate command:

wp wc palt regenerate

This one runs synchronously on the command line rather than through the Action Scheduler, which avoids the stuck-regeneration problem that plagues the admin UI on larger stores.

After running both, I verified the fix:

SELECT COUNT(*) AS still_desynced
FROM wp_posts p
JOIN wp_postmeta pm_price
    ON p.ID = pm_price.post_id AND pm_price.meta_key = '_price'
JOIN wp_wc_product_meta_lookup lookup
    ON p.ID = lookup.product_id
WHERE p.post_type = 'product'
    AND p.post_status = 'publish'
    AND CAST(pm_price.meta_value AS DECIMAL(10,2)) != lookup.min_price;

Zero. The shop page immediately showed correct prices, sort by price worked, and the On Sale filter picked up all 87 products that were actually on sale.

Preventing It From Happening Again

Regenerating the lookup table is a fine emergency fix, but the real solution is making sure it doesn't desync in the first place. I set up three safeguards for this client.

1. Post-import verification script

A WP-CLI command that runs after every CSV import to check for desyncs and auto-repair:

#!/bin/bash
PREFIX=$(wp db prefix --skip-plugins --skip-themes 2>/dev/null)
DESYNCED=$(wp db query "
    SELECT COUNT(*)
    FROM ${PREFIX}posts p
    JOIN ${PREFIX}postmeta pm ON p.ID = pm.post_id AND pm.meta_key = '_price'
    JOIN ${PREFIX}wc_product_meta_lookup lu ON p.ID = lu.product_id
    WHERE p.post_type = 'product'
    AND p.post_status = 'publish'
    AND CAST(pm.meta_value AS DECIMAL(10,2)) != lu.min_price
" --skip-column-names 2>/dev/null | tr -d '[:space:]')

if [ "$DESYNCED" -gt 0 ]; then
    echo "Found $DESYNCED desynced products. Regenerating lookup tables..."
    wp wc tool run regenerate_product_lookup_tables --user=1
    wp wc palt regenerate
    echo "Lookup tables regenerated."
else
    echo "All products in sync."
fi

2. Weekly cron check

I added a system cron job that runs the verification script every Sunday night, catching any drift from plugins or background processes:

0 3 * * 0 cd /var/www/html && /bin/bash /var/www/scripts/check-lookup-sync.sh >> /var/log/woocommerce-lookup-check.log 2>&1

3. Increased PHP memory for imports

The root cause of the original desync was a memory exhaustion during import. I bumped the PHP memory limit for both CLI and the web-facing PHP-FPM pool. CLI gets the higher limit because that's where WP-CLI bulk operations run, but WooCommerce's built-in CSV importer actually processes batches via AJAX through the web PHP runtime — so the FPM pool needs headroom too:

; /etc/php/8.2/cli/conf.d/99-woocommerce.ini
memory_limit = 512M

; /etc/php/8.2/fpm/conf.d/99-woocommerce.ini
memory_limit = 384M

512M for CLI covers WP-CLI imports and cron tasks. 384M for FPM gives the admin CSV importer enough room to finish without exhausting memory mid-batch, without being wasteful across all web requests.

Symptoms That Should Make You Check the Lookup Table

If you manage WooCommerce stores, keep an eye out for these. They all point to a lookup table desync:

• Sort by price produces random-looking order — the lookup table's min_price doesn't match the actual product price
• "On sale" filter misses products — the onsale flag in the lookup table is 0 even though _sale_price is set in postmeta
• Stock status filters are wrong — a product shows "in stock" on its page but doesn't appear when filtering by "in stock" on the shop
• Product counts in filters don't add up — attribute and price filter widgets show wrong counts because the lookup tables hold stale data
• Prices on listings differ from product pages — the canonical sign of this issue

Any time a client reports one of these after a bulk import, a plugin update, or an ERP sync, the lookup table is the first thing I check. It takes 30 seconds to verify and 60 seconds to fix.

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 →