The 15-Filter Query That Broke Our MySQL Search

The short version Faceted search (full-text plus many range and equality filters plus live counts, all freely combinable) is a set-intersection problem, not a relational lookup. MySQL can't cover arbitrary filter combinations with one index, and every facet count is a separate GROUP BY. A search engine like Meilisearch or Typesense answers the whole thing in one request over bitmaps. Move when filters get many and free-form; stay on MySQL when they're few and fixed.

The 15-filter query that broke us

Our audio search grew past 15 filters, and the day we let users combine them freely, latency slid from milliseconds into whole seconds. Full-text on title, description, and tags; ranges on duration, BPM, and sample rate; facets on genre, key, instrument, and license.

The database CPU spiked on every keystroke. MySQL would pick one index, scan everything else row by row, and every facet count next to the results was another query on top.

The fix wasn't a smarter index. It was accepting that faceted, multi-filter search is a different problem than relational lookup.

Why does MySQL slow down with many filters?

MySQL slows down because one query leans on a single index and checks every other condition by reading rows, and no index serves a full-text match plus a dozen filters together. The killer isn't any one filter. It's the combinations.

A user searches "warm analog pad," caps BPM at 90 to 120, duration under 30 seconds, picks two genres and a musical key, and excludes anything not cleared for commercial use. One request touches a full-text match, two range conditions, and several equality conditions at once.

SELECT id, title
FROM samples
WHERE MATCH(title, description, tags) AGAINST('warm analog pad' IN NATURAL LANGUAGE MODE)
  AND bpm BETWEEN 90 AND 120
  AND duration_ms < 30000
  AND genre IN ('ambient', 'downtempo')
  AND musical_key = 'Am'
  AND license = 'commercial'
ORDER BY /* relevance */ ;

For a query like this, MySQL commits to one index (its main way into the rows), then checks the leftover conditions by reading each row it fetched. Its index_merge optimization can union or intersect a couple of B-tree indexes, but it won't combine them with a FULLTEXT search, and it doesn't stretch across this many arbitrary conditions.

A composite index like (genre, musical_key, bpm) only helps when the query leads with those columns in that order. Since users pick filters in any combination, covering them all means a combinatorial pile of composite indexes you'll never finish building, each one slowing writes. So the planner commits to one path and the rest degrades into a scan.

You don't have to guess which path it took. EXPLAIN ANALYZE runs the query and reports the actual access method and rows read, so you can see the one index it chose and the conditions it applied by reading rows afterward.

EXPLAIN ANALYZE
SELECT id FROM samples
WHERE MATCH(title, description, tags) AGAINST('warm analog pad' IN NATURAL LANGUAGE MODE)
  AND bpm BETWEEN 90 AND 120
  AND license = 'commercial';
-- fulltext match drives the row lookup; bpm + license are applied as a post-filter, not an index seek

Facet counts multiply your queries

A faceted UI shows a count next to every option, and each count is its own aggregate query. 240 ambient, 85 downtempo, 1,200 tracks between 90 and 120 BPM.

-- one of these per facet dimension, re-running the same filter set
SELECT genre, COUNT(*)
FROM samples
WHERE MATCH(title, description, tags) AGAINST('warm analog pad' IN NATURAL LANGUAGE MODE)
  AND bpm BETWEEN 90 AND 120
  AND license = 'commercial'
GROUP BY genre;

With a dozen facet dimensions plus min/max lookups for the range sliders, one user interaction fans out into fifteen-odd queries, each re-applying the full filter set.

You can cache, denormalize, or precompute, but now you're maintaining a second system by hand inside your primary database, and it still fights your write traffic for the same CPU.

Why can't one index cover every filter?

A composite index is a bet on one filter order, and faceted search is every order at once. It sorts rows by the first column, then the second within that, and so on, which is why it only helps when the query's filters line up with that leading run.

Faceted search is the opposite shape: any subset of filters, in any order, plus counts across the whole matching set.

A composite index is a bet on one filter order. Faceted search is every order at once, which is a set-intersection problem, not a sorted-index one.

The structure that fits is an inverted index (a map from each search term to the documents that contain it) paired with a bitmap per filter value. Each value (genre = ambient, license = commercial) becomes a row of bits, one bit per document, set to 1 where the document matches. A range filter is the union of the bitmaps in range.

Combining 15 filters is then just AND-ing and OR-ing those rows of bits, and a facet count is the number of 1s left after the AND. That is the operation faceted search actually needs, and a relational engine isn't built for it.

doc id            1  2  3  4  5  6  7  8
genre=ambient     1  0  1  1  0  1  0  0
bpm 90-120        1  1  1  0  0  1  0  1
license=comm.     1  0  1  1  1  1  0  0
                  ───────────────────────  AND
matches           1  0  1  0  0  1  0  0   → docs 1, 3, 6
                                            1s left = 3  (the facet count)

Each row of bits is one filter. Intersecting them is a bitwise AND, and the number of 1s left is the count you show in the UI. No row-by-row scan, no separate GROUP BY.

How does a dedicated search engine solve it?

When MySQL hits this wall, the fix is architectural: move search to an engine built on the inverted-index-and-bitmap model instead of the sorted B-tree. Meilisearch, Typesense, and Algolia are all built this way, so adopting one is the real solution, not another round of index tuning.

They build two structures side by side at index time. The engine tokenizes your text into an inverted index, and for every filterable attribute it builds a compact bitmap: one row of bits per value, plus range-friendly structures for numeric fields like BPM and duration. (These are typically roaring bitmaps, a compressed bitmap format, so they stay small even across millions of documents.)

At query time it runs a single pass. The text query resolves to a candidate set of document ids, each active filter contributes its bitmap, and the engine intersects them all with AND and OR, ranks the survivors, and derives every facet count from that same set by counting bits. Hits, facet counts, and range min/max come back together, from one pass.

Concretely, in Meilisearch you mark fields as filterableAttributes, then a single search request carries a filter and a facets list and returns hits plus facet counts computed over the filtered set:

{
  "q": "warm analog pad",
  "filter": "bpm 90 TO 120 AND license = 'commercial'",
  "facets": ["genre", "musical_key", "license"]
}

Typesense is the same shape: one /documents/search call with filter_by (equality, [min..max] ranges, &&/||) and facet_by returns hits and facet_counts in one response.

That is why the combinatorial problem disappears. There's no composite index to guess at, because filters aren't index prefixes, they're bitmaps you intersect in any order. There's no per-facet GROUP BY, because a count is just the bits left in the result. You declare which fields are filterable once, the engine keeps those structures in sync as documents change, and the planner you were fighting is out of the loop.

The services, and what they cost

All three managed options do faceted, multi-filter search well; they mostly differ in how you pay. Prices drift, so check the linked pages before you budget.

Meilisearch is open source (MIT), so self-hosting is free and you own backups and scaling. Meilisearch Cloud is the managed tier, billed either usage-based (documents plus searches) or resource-based (a fixed instance size), starting in the low tens of dollars a month.

Typesense is also open source and self-hostable. Typesense Cloud bills purely on the RAM and CPU you provision, with no per-search charge and no cap on records or operations, so a traffic spike doesn't turn into a surprise invoice.

Algolia is the fully managed, batteries-included end. Its pricing is usage-based on search requests and records, with a free tier fine for development and a curve that climbs steeply at high query volume. You pay for the smoothest onboarding plus built-in personalization and merchandising.

If you're already in the Elastic ecosystem, Elasticsearch or OpenSearch will also do this, but for product search they're usually more operational weight than the job needs.

When to keep MySQL, and when not to

Keep search in MySQL when the filters are few and fixed: a handful of equality columns you can cover with one or two composite indexes, and no facet counts in the UI. That's a lookup, and adding a search engine is complexity you don't need.

Move the moment users combine many filters freely, mix full-text with ranges, or expect live facet counts. That's not a query you can index your way out of. It's a set-intersection workload wearing a SQL costume, so make the switch deliberately.

The caveat to go in with: a search index is a second datastore, and now you own keeping it in sync with your source of truth, through change events or periodic reindexing. A stale index is its own class of bug. It was a good trade for the audio search we ran, but be clear-eyed that you're swapping a query-planning problem for a synchronization one.

FAQ