Cut a 300s Product Filter Query to 30s With a Lookup Table

TL;DR A product filter query joining a 2.5M-row products table to a 12M-row category pivot ran up to 300s. The fix: copy every filter column into one denormalized lookup table with a single covering index, so the whole WHERE clause hits one table instead of two. Runtime fell to ≤30s, a 10× cut.

The query that took five minutes

This is the query that pinned a database CPU for up to 300 seconds at a time:

SELECT id FROM products
WHERE status = 2
  AND vendor_id = 2
  AND is_books = true
  AND has_qty = 1
  AND language IN ('English')
  AND EXISTS (
    SELECT 1 FROM product_categories pc
    WHERE pc.category_id IN (8241, 8467)
      AND pc.product_id = products.id
  )
  AND bc_suggested_price BETWEEN 0 AND 192.99;

The shape is ordinary product filtering, the kind of multi-filter search a store's sidebar drives (the industry term is faceted search): filter products by a handful of attributes, restrict to a couple of categories, bound the price. The scale is what breaks it. products holds ~2.5M rows. Every product sits in at least 5 categories out of ~100k total, so the product_categories pivot is 12M+ rows.

The fix that held up moved the whole thing to a single denormalized lookup table and dropped the runtime to ≤30 seconds, a 10× cut. Here's why the obvious index tuning didn't get there, and what did.

Why the EXISTS category join is slow

The query is slow because the filter conditions live in two tables, so no single index can serve the whole WHERE clause. The optimizer (the part of MySQL that decides how to run a query) has to pick one table to read first, then jump to the matching rows in the other. At 2.5M products against a 12M-row pivot, both directions lose.

Play out the two plans. If MySQL reads products first, it filters on the attribute columns, then for every surviving row it looks up that product in the pivot to check its category: potentially millions of tiny lookups.

If it reads product_categories first (MySQL 8.0 can turn an EXISTS into a join automatically), it pulls every product_id in categories 8241 and 8467, then goes back into products to check status, vendor_id, price, and the rest: one random row read per candidate. Either way, one table is scanned in bulk and the other is hit one row at a time.

The BETWEEN on price makes it worse: it's a range, so it can only be used after the equality checks, and it can't be applied across the join to the other table at all.

The bottleneck isn't a missing index on either table. It's the boundary between the two tables, and every candidate row has to cross it, and there are millions of candidates.

Why adding more indexes didn't help

Adding indexes made it somewhat faster but didn't change the plan. I threw a wider composite (multi-column) index at products and a covering index at product_categories, one that holds every column the query needs, so MySQL can answer from the index without reading the table. It didn't matter: you still have two separate indexes and a join between them, and MySQL still looks up one for every row that survives the other.

A single index only helps when the columns it uses are all in one table and sit at the start of the index, in order. MySQL can use any leading run of a multi-column index: an index on (col1, col2, col3) works for filters on col1, (col1, col2), or all three, but it cannot combine an index on products with an index on product_categories into one read.

The category_id filter and the status/price filters sit in different tables joined together, so no index on either table alone can cover the whole query. That's the ceiling. To break it you have to remove the join, not add more indexes around it.

The fix: one denormalized lookup table

Collapse the two tables into one. This is denormalization: deliberately storing duplicate data so a query can skip the join. Build a lookup table that carries the category_id alongside a copy of every column you filter products on, with one row per (category, product) membership:

CREATE TABLE product_filter_lookup (
  category_id        INT UNSIGNED    NOT NULL,
  status             TINYINT         NOT NULL,
  vendor_id          INT UNSIGNED    NOT NULL,
  is_books           TINYINT(1)      NOT NULL,
  has_qty            TINYINT(1)      NOT NULL,
  `language`         VARCHAR(16)     NOT NULL,
  bc_suggested_price DECIMAL(10,2)   NOT NULL,
  product_id         BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (category_id, product_id),        -- one row per membership; clusters by category
  KEY idx_facet (                               -- the workhorse: covering, for the filter query
    category_id, status, vendor_id, is_books,
    has_qty, `language`, bc_suggested_price, product_id
  )
);

The PRIMARY KEY (category_id, product_id) gives the table an identity, blocks duplicate membership rows, and clusters the data by category so a rebuild writes sequentially. idx_facet is the index the query actually uses; the primary key just stops duplicate rows from sneaking in.

Yes, this copies the product columns once per category the product belongs to, so the table ends up roughly the same 12M+ rows as the pivot, now carrying the filter columns too. That duplication is the whole point: the entire WHERE clause now lives in one table, so one index can answer it.

Match the column types and collations to the source exactly. If product_filter_lookup.language uses a different character set or collation than the value you compare it against, MySQL silently converts types and stops using the index. The query becomes single-table and index-only:

SELECT DISTINCT product_id
FROM product_filter_lookup
WHERE category_id IN (8241, 8467)
  AND status = 2
  AND vendor_id = 2
  AND is_books = 1
  AND has_qty = 1
  AND `language` = 'English'
  AND bc_suggested_price BETWEEN 0 AND 192.99;

Note the DISTINCT: a product tagged to both 8241 and 8467 has two rows in the lookup table, so it matches twice. That dedupe isn't quite free. Within a category slice the index orders rows by price then product_id, not by product_id alone, so MySQL still does a small sort/hash to collapse duplicates. It runs over an already-tiny, index-only result, so the cost is tiny next to the scan it replaced, but it isn't zero.

Getting the composite index order right

The column order in idx_facet is not random; it's the difference between an index scan and an index-only scan, and between using the whole index and using half of it. Three rules drove it:

One continuous range, and it goes last. MySQL walks a B-tree index left to right and can only apply one continuous range before it stops narrowing; every column after that range is no longer used to seek, only (at best) to filter. bc_suggested_price BETWEEN 0 AND 192.99 is a continuous range, so it sits after every equality column. status, vendor_id, is_books, has_qty, and language IN ('English') are single-value equalities, so the index chains them into one point before the range narrows within it.

IN with several values is not that kind of range. This is the distinction people miss. category_id IN (8241, 8467) is a many-valued equality (MySQL's "equality range optimization"), not a continuous range like BETWEEN. The optimizer dives once per value and, crucially, keeps using the following key parts as equalities inside each dive. So leading with category_id IN (...) does not forfeit status, vendor_id, and the rest the way a BETWEEN in that slot would. One caveat: if that IN list ever grows past eq_range_index_dive_limit (default 200), MySQL stops diving and falls back to index statistics, and row estimates get rougher.

Lead with the column that cuts the most rows. Here that's category_id: 2 of ~100k categories is a tiny slice, so it leads. Confirm it against your own data with EXPLAIN; the only hard rule is that the continuous range comes after the equalities, not which equality is first.

The selected column trails the range, for coverage only. product_id is the final column purely so it lives in the index leaf and SELECT product_id never touches the table rows. It sits after the range, so it does no narrowing, which is fine; coverage doesn't require it to. In EXPLAIN this shows up as Using index in the Extra column, which is the signal you want: no extra reads back into the table data.

Equalities first, one continuous range last, the selected column trailing behind it for coverage. Put the range too early and MySQL silently stops seeking on every column after it.

What it costs: storage and staying in sync

This is a read optimization you pay for at write time, and it's not free. You now store the filterable product columns 5+ times over, once per category each product belongs to, so the lookup table is noticeably larger than the pivot it replaces. Budget the disk.

The sharper cost is correctness over time: the lookup table is a copy, and copies drift. Every change to a product's status, price, language, or category memberships has to be copied across, or your filters start lying. You have three options, roughly in order of how stale you can let the data get.

Rebuild the whole table on a schedule (simplest, stalest, fine if catalog data changes hourly, not if it changes per request). Keep it current with triggers on products and product_categories (immediate, but triggers on a 2.5M-row table add write latency and are easy to get subtly wrong). Or dual-write from the application layer inside the same transaction that mutates the product (most control, most code).

Whichever you pick, the lookup table is derived data. Treat it as a cache you can always rebuild from the source tables, never as a second source of truth.

FAQ

When this is worth it

Reach for a denormalized lookup table only when your filter conditions live in two tables and no single index can cover them. That's the specific condition this solves. If everything you filter on is already in one table and the query is still slow, fix the index order first: equality columns, then the range, then the selected id. You probably don't need a second copy of your data. Duplicate the columns only once you've proven the join boundary itself is the wall, because from then on every write pays for the read.