Filtering a WooCommerce catalogue without the meta tables

The shop had about 4,000 products carrying roughly 30,000 variations, and a filter sidebar with five facets: brand, frame shape, material, colour and price. Selecting one facet was slow. Selecting three did not return at all.

The symptom

# Query_time: 38.114  Lock_time: 0.001  Rows_sent: 12  Rows_examined: 8814402
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts
  INNER JOIN wp_postmeta AS mt1 ON wp_posts.ID = mt1.post_id
  INNER JOIN wp_postmeta AS mt2 ON wp_posts.ID = mt2.post_id
  INNER JOIN wp_postmeta AS mt3 ON wp_posts.ID = mt3.post_id
 WHERE mt1.meta_key = 'attribute_pa_material' AND mt1.meta_value = 'acetate'
   AND mt2.meta_key = 'attribute_pa_colour'   AND mt2.meta_value = 'tortoise'
   AND mt3.meta_key = '_price'                AND mt3.meta_value BETWEEN 50 AND 200

Eight point eight million rows examined to return twelve. The Rows_examined figure roughly cubes as facets are added, which is the shape of the problem in one number.

Why it happens

WordPress stores custom fields in wp_postmeta, one row per key per post — an entity-attribute-value layout. It is a good trade for a CMS, where the set of fields is unknown and mostly unqueried. It is a poor one for a catalogue, where the fields are known, fixed, and queried together on every page.

Each additional filter means another self-join against the same table. The meta_key index narrows each join, but the intermediate result sets multiply, and meta_value is a LONGTEXT column that cannot be usefully indexed for a range comparison — which is why the price filter is the one that pushes it over the edge.

Why not just index postmeta

The obvious first move is to index the meta table harder, and it is worth understanding why that does not work before building anything. wp_postmeta already carries an index on meta_key, and adding a composite over (meta_key, meta_value(32)) does measurably help a single-facet query.

# one facet, with the composite index in place
Rows_examined: 3104   Query_time: 0.21

# three facets, same index
Rows_examined: 8814402   Query_time: 38.11

The index narrows each join and does nothing about the number of joins. Three facets still mean three passes over the same table producing three intermediate sets that must then be intersected, and the prefix length caps how much of a value is indexed at all — which is why the price range, a numeric comparison against a text column, cannot use it in either direction.

The shape of the data is wrong for the question being asked, and no index fixes a shape. That is the argument for a second table rather than a better index on the first.

The fix

One row per variation, one column per facet

The filters are known in advance, so the table can be shaped like the query instead of like the CMS. Every facet becomes a column, every variation a row, and the five-way join collapses into one WHERE clause over a composite index.

CREATE TABLE wp_product_filter_index (
  variation_id  BIGINT UNSIGNED NOT NULL,
  product_id    BIGINT UNSIGNED NOT NULL,
  brand_id      INT UNSIGNED NULL,
  shape_id      INT UNSIGNED NULL,
  material_id   INT UNSIGNED NULL,
  colour_id     INT UNSIGNED NULL,
  price_cents   INT UNSIGNED NOT NULL,
  in_stock      TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (variation_id),
  KEY idx_facets (in_stock, brand_id, material_id, colour_id, price_cents),
  KEY idx_price  (in_stock, price_cents),
  KEY idx_parent (product_id)
) ENGINE=InnoDB;

Note the column order in idx_facets. in_stock comes first because it is on every query; price comes last because it is a range, and a range condition ends the usable prefix of a composite index — anything after it cannot be used for lookup.

The same query against that table:

SELECT DISTINCT product_id
  FROM wp_product_filter_index
 WHERE in_stock = 1
   AND material_id = 4
   AND colour_id = 11
   AND price_cents BETWEEN 5000 AND 20000
 ORDER BY price_cents
 LIMIT 24;

Keeping it in sync

A second source of truth is a liability unless something guarantees it tracks the first. The hooks that matter are fewer than expected, because everything funnels through a small number of save paths.

add_action( 'woocommerce_update_product', 'shop_reindex_product' );
add_action( 'woocommerce_new_product',    'shop_reindex_product' );
add_action( 'woocommerce_delete_product', 'shop_deindex_product' );
add_action( 'before_delete_post',         'shop_deindex_product' );

// stock moves without the product being saved
add_action( 'woocommerce_variation_set_stock',  'shop_reindex_variation' );
add_action( 'woocommerce_product_set_stock',    'shop_reindex_product' );

The stock hooks are the ones that get missed. An order reduces stock without firing a product save, so an index built only on woocommerce_update_product keeps showing sold-out variations until someone edits the product by hand.

function shop_reindex_product( $product_id ) {
    global $wpdb;

    $product = wc_get_product( $product_id );

    if ( ! $product ) {
        return;
    }

    $children = $product->is_type( 'variable' )
        ? $product->get_children()
        : array( $product_id );

    $wpdb->delete( $wpdb->prefix . 'product_filter_index', array( 'product_id' => $product_id ) );

    foreach ( $children as $variation_id ) {
        $variation = wc_get_product( $variation_id );

        $wpdb->insert(
            $wpdb->prefix . 'product_filter_index',
            array(
                'variation_id' => $variation_id,
                'product_id'   => $product_id,
                'brand_id'     => shop_term_id( $product_id, 'pa_brand' ),
                'material_id'  => shop_term_id( $variation_id, 'pa_material' ),
                'colour_id'    => shop_term_id( $variation_id, 'pa_colour' ),
                'price_cents'  => (int) round( $variation->get_price() * 100 ),
                'in_stock'     => $variation->is_in_stock() ? 1 : 0,
            ),
            array( '%d', '%d', '%d', '%d', '%d', '%d', '%d' )
        );
    }
}

Delete-then-insert per product rather than a diff. It is more writes, but a variation removed from a product disappears from the index automatically, and correctness at this layer is worth more than the saving.

The rebuild command

The hooks keep it current. Something has to build it in the first place, repair it after a bulk import that bypassed the hooks, and prove that it can be regenerated at all.

$ wp shop reindex --all
Indexing 4,112 products (29,884 variations)
  100% [==============================] 0:03:41
Success: 29,884 rows written.

$ wp shop reindex --verify
Checked 29,884 rows against source data.
Success: no drift.

The --verify mode is the part worth building. It reads both sides and reports differences without writing anything, which turns “the index might be stale” from a suspicion into a check that can run nightly.

The facet counts are a second problem

A filter sidebar does not only need the matching products. It needs the number beside each unopened facet — “Acetate (312)” — and computing those naively means running the whole filter query once per remaining option. Five facets with eight options each is forty extra queries per page load.

Against a single flat table it is one grouped query per facet, over the same index, restricted by whatever is already selected.

-- counts for the material facet, given brand and colour already chosen
SELECT material_id, COUNT(DISTINCT product_id) AS n
  FROM wp_product_filter_index
 WHERE in_stock = 1
   AND brand_id  = 17
   AND colour_id = 11
 GROUP BY material_id;

The subtlety is which selections to apply. Counting the material facet must not filter by material, or every option except the selected one reports zero and the sidebar becomes unusable. Each facet is counted with every other facet’s selection applied but not its own — the behaviour customers expect, and almost nobody implements first time.

Bulk imports go round the hooks

The hooks fire on the WooCommerce save path. A nightly supplier import writing with $wpdb->update() for speed never goes near it, so the index quietly stops matching reality — and since prices and stock are exactly what an import changes, the drift lands in the columns customers filter on.

Two defences, both needed. The import reindexes what it touched:

// at the end of each import batch
foreach ( $touched_product_ids as $id ) {
    shop_reindex_product( $id );
}

And the nightly verify runs after it, reporting rather than repairing — because a silent repair hides the fact that something upstream is not calling the hooks at all.

$ wp shop reindex --verify
Checked 29,884 rows against source data.

DRIFT: 41 rows
  price_cents mismatch  38
  in_stock mismatch      3

Source: last write 03:14 (supplier-import)

The timestamp on the last write is what turns a number into a lead. Forty-one rows drifting at 03:14 every night points at one job, not at a general correctness problem.

Verifying it worked

# three facets plus a price range, 100 requests, concurrency 5

# before
Requests per second:    0.03 [#/sec]
Time per request:       33481 [ms]
Failed requests:        41   (gateway timeouts)

# after
Requests per second:    61.20 [#/sec]
Time per request:       81 [ms]
Failed requests:        0

Thirty-three seconds to eighty-one milliseconds, and Rows_examined down from 8.8 million to 340. The filter also stopped degrading as facets were added, which was the actual complaint — a query that gets slower with each selection teaches customers not to use the filters.

Rolling it out without a flag day

The index is a parallel structure, which means it can be built and proved before anything reads from it. That is worth using: the rebuild ran nightly for a week against production data while the site still filtered through the old query.

// during the trial week: answer from the old path, compare with the new
$legacy = $this->metaQuery( $filters );
$fast   = $this->indexQuery( $filters );

if ( $legacy !== $fast ) {
    error_log( sprintf(
        'filter drift: %s | legacy %d, index %d',
        wp_json_encode( $filters ),
        count( $legacy ),
        count( $fast )
    ) );
}

return $legacy;   // still the source of truth

Running both and logging the difference turned the cutover from a decision into an observation. Two mismatches surfaced in the first two days — both variations with a null price that the meta query included and the index excluded, because price_cents was NOT NULL and the reindex had silently written a zero. Neither would have been noticed in testing, and both would have been reported as missing products the morning after a flag-day switch.

What this costs

There are now two representations of the same data and they can disagree. Every future code path that writes product data — an importer, a bulk-edit plugin, a direct $wpdb update in someone’s snippet — has to go through the hooks or the index rots quietly. The nightly verify is not optional; it is the thing that makes the trade acceptable.

Saving a variable product with sixty variations is also measurably slower now, because sixty rows are rewritten. That is the right place to pay it: an admin waiting an extra second on save, rather than every customer waiting on every filter click.

The third cost is the one that will outlast the other two. This table encodes the current set of facets as columns, so adding a sixth is a migration rather than a configuration change — which is precisely the flexibility that was traded away, and it was the right trade only because the facets had been stable for two years. A shop whose merchandising team invents new filterable attributes every quarter would be worse off here than with the meta tables, and should be looking at a search index instead, where a schema change is a reindex rather than an ALTER TABLE.

Worth saying plainly: none of this is a criticism of how WooCommerce stores data. The meta table is the right structure for a plugin that cannot know what fields a shop will invent, and it stays correct at every size — it just stops being fast at this one. The generalisable lesson is that a read pattern this specific and this hot eventually earns a structure of its own, and that recognising the point at which it does is worth more than any particular index.