The filtered catalogue page took 6.4 seconds against 24,000 products, and the query behind it joined wp_postmeta six times. Nothing about that was a mistake — it is what the data model requires — and WooCommerce 3.6 in April is the release that stops requiring it, partially.
The symptom
mysql> EXPLAIN SELECT p.ID FROM wp_posts p
-> INNER JOIN wp_postmeta m1 ON m1.post_id = p.ID
-> INNER JOIN wp_postmeta m2 ON m2.post_id = p.ID
-> INNER JOIN wp_postmeta m3 ON m3.post_id = p.ID
-> WHERE p.post_type = 'product' AND p.post_status = 'publish'
-> AND m1.meta_key = '_stock_status' AND m1.meta_value = 'instock'
-> AND m2.meta_key = '_price' AND m2.meta_value BETWEEN 10 AND 50
-> AND m3.meta_key = '_visibility'
-> ORDER BY p.post_date DESC LIMIT 24;
| table | type | rows | Extra |
| m2 | ref | 1841204 | Using where; Using temporary; |
| | | | Using filesort |1.8 million rows examined for twenty-four results, a temporary table and a filesort. The _price comparison is the worst of it: meta_value is a longtext, so a numeric range casts every row and cannot use the index at all.
Why it happens
Every product attribute is a row in a table shared by every post on the site — orders, pages, revisions, everything. The index is on meta_key(191), post_id, which is designed for “give me this key for this post” and is the wrong shape for “give me every post where this key has this value”.
A shop with 24,000 products and thirty meta keys each has 720,000 rows before anything else on the site is counted. The joins are not the problem in themselves; the problem is that each one lands on a large table with an index that cannot narrow it.
The fix
What the lookup table contains
mysql> DESCRIBE wc_product_meta_lookup;
+------------------+---------------+
| product_id | bigint |
| sku | varchar(100) |
| virtual | tinyint(1) |
| downloadable | tinyint(1) |
| min_price | decimal(19,4) |
| max_price | decimal(19,4) |
| onsale | tinyint(1) |
| stock_quantity | double |
| stock_status | varchar(100) |
| rating_count | bigint |
| average_rating | decimal(3,2) |
| total_sales | bigint |
+------------------+---------------+Typed columns with real indexes, one row per product, and min_price and max_price as decimals rather than strings — which is what makes a price range a range scan instead of a full cast. The variable-product case is why there are two price columns: a variable product’s price is a range, and postmeta had no way to express that without a second key.
-- the same catalogue query, 3.6
SELECT p.ID FROM wp_posts p
INNER JOIN wc_product_meta_lookup l ON l.product_id = p.ID
WHERE p.post_type = 'product' AND p.post_status = 'publish'
AND l.stock_status = 'instock'
AND l.min_price BETWEEN 10 AND 50
ORDER BY p.post_date DESC
LIMIT 24;The regeneration, which is slow and unannounced
The table is populated by a background job on upgrade, and on a large catalogue that takes hours — during which queries return partial results and nobody has been told.
# what runs on its own, in the background, after the upgrade
$ wp option get woocommerce_maybe_regenerate_product_lookup_tables
1
# what to run instead, deliberately, in a window
$ wp wc tool run regenerate_product_lookup_tables --user=1
$ watch -n5 "wp db query 'SELECT COUNT(*) FROM wc_product_meta_lookup' --skip-column-names"
# and the check that it finished
$ wp db query "SELECT
(SELECT COUNT(*) FROM wp_posts WHERE post_type='product' AND post_status='publish') AS products,
(SELECT COUNT(*) FROM wc_product_meta_lookup) AS looked_up"Comparing the two counts is the only way to know it is complete, because nothing reports completion anywhere visible. A shop that upgrades on a Friday and starts showing a partial catalogue on Saturday is a plausible outcome of doing nothing, and it looks like a caching problem rather than a migration in progress.
Querying it directly, and what that commits you to
// the supported route: WooCommerce builds the query
$products = wc_get_products( array(
'status' => 'publish',
'stock_status' => 'instock',
'limit' => 24,
) );
// the fast route, for a listing that does not need objects
global $wpdb;
$ids = $wpdb->get_col( $wpdb->prepare(
"SELECT l.product_id FROM {$wpdb->prefix}wc_product_meta_lookup l
INNER JOIN {$wpdb->posts} p ON p.ID = l.product_id
WHERE p.post_status = 'publish'
AND l.stock_status = 'instock'
AND l.min_price BETWEEN %f AND %f
ORDER BY l.total_sales DESC LIMIT 24",
$min,
$max
) );The second form is dramatically faster and it couples the code to a table WooCommerce has not promised to keep stable — it is documented as an implementation detail. That is a real risk and it is smaller than it sounds, because the table is now load-bearing for the plugin’s own queries and cannot change casually. Confining the raw SQL to one class is what makes the eventual adjustment a single edit.
What is not in it
Anything not in those twelve columns still lives in postmeta, which means a shop filtering on a custom attribute gains nothing at all from the upgrade.
in the lookup table sku, price range, stock, sales, rating,
on-sale, virtual, downloadable
still postmeta every custom field
product attributes (taxonomies, actually)
anything a plugin added
everything about orders
the shop that gained nothing: filtering by a custom
'lead_time_days' meta key, which is where its slow query was.Product attributes being taxonomies rather than meta is worth knowing separately, because taxonomy queries are indexed properly and are usually fine — the shops that are slow are the ones filtering on custom meta. For those, the honest answer is a custom table of your own with the columns and indexes the query needs, and accepting that it sits outside the WordPress data model.
Verifying it worked
$ curl -s -o /dev/null -w '%{time_total}n' 'https://shop.example/shop/?min_price=10&max_price=50'
0.412 # was 6.412
mysql> EXPLAIN SELECT ... ;
| table | type | rows | Extra |
| l | range | 1841 | Using where |
# and the count that says the table is complete
$ wp db query "SELECT ... products, looked_up"
products looked_up
24014 24014Rows examined dropping from 1.8 million to 1,841 is the number that explains the page time, and it is worth capturing in the ticket rather than only the wall clock — the wall clock varies with cache state and the row count does not. The equal counts are the other half, and they are what distinguishes a fast page from a fast page showing three quarters of the catalogue.
What this costs
Two sources of truth and a job that keeps them agreeing. The lookup table is derived data, and derived data drifts — a plugin that writes _price directly with update_post_meta updates the meta and not the lookup, and the catalogue then shows a price the product page contradicts. WooCommerce hooks the CRUD layer to keep them in step, which works for everything going through WC_Product and nothing else. Auditing plugins for direct meta writes is worth doing once and is not a pleasant afternoon.
The broader observation is that this is a plugin working around its own data model, and the workaround is correct. Orders have the same problem and are considerably worse — every order is a post with twenty meta rows, and a shop with 200,000 orders has four million rows in a table shared with the entire site. A custom order table has been discussed for long enough that betting against it is a poor trade, and the CRUD layer introduced in 3.0 is what makes it possible without breaking every plugin.