Full-text search that did not need a search engine

The product search ran against a three-node Elasticsearch cluster that existed for one feature and forty thousand rows. It had been added in 2018 because search is what a search engine is for, and the Elasticsearch 8 upgrade in February — the TLS, the CA distribution, the client rewrite — was the moment somebody asked whether it was still earning the operational cost.

The symptom

$ curl -s localhost:9200/_cat/indices?v
health index      docs.count  store.size
green  products         41208      88.4mb

# one index, 41,208 documents, 88 MB.

# the cluster it lives on:
#   3 nodes, 4 GB heap each
#   a CA to distribute and renew
#   a client library rewritten for 8.x
#   a runbook, a backup, and a place in the on-call rotation

Eighty-eight megabytes of documents on twelve gigabytes of heap is not a capacity problem, it is a question about whether the component belongs. Answering it required knowing what the search actually did, which nobody had written down since 2018.

Why it happens

A search engine is the obvious answer to “we need search” and is the right answer above a threshold that nobody establishes at the time. The decision is made once, at the start, when the data is small and the requirements are a paragraph — and it is never revisited because it works.

The fix

Reading what the queries actually do

a week of the slow log, at threshold 0, categorised:

  multi_match over name + description   91%
  a term filter on category or brand    74%
  a range filter on price               38%
  sort by price or created_at           22%
  aggregations, fuzziness, more_like_this, percolate   0%

no faceting, no fuzzy matching, no relevance tuning
beyond the defaults. it is a filtered keyword search.

Zero use of aggregations is the number that decided it. Faceted counts over a large filtered set are the thing a search engine genuinely does better than a relational database, and their complete absence meant the remaining requirements were within what MySQL can do.

What MySQL full-text actually gives you

ALTER TABLE products ADD FULLTEXT INDEX ft_search (name, description);

SELECT id, name,
       MATCH(name, description) AGAINST(? IN NATURAL LANGUAGE MODE) AS score
FROM products
WHERE MATCH(name, description) AGAINST(? IN NATURAL LANGUAGE MODE)
  AND category_id = ? AND price_cents BETWEEN ? AND ?
ORDER BY score DESC
LIMIT 24;

and the three limits that decide whether this works:

  minimum word length   innodb_ft_min_token_size = 3.
                        'XL' matches nothing. server-level,
                        and changing it rebuilds every index.

  50% threshold         in natural language mode, a term in
                        more than half the rows is ignored.
                        a brand name usually is.

  scoring               term frequency, summed. no field
                        weighting, no length normalisation.

The fifty per cent threshold is the one that produces a search returning nothing for the most obvious query, and it applies only in natural language mode. Boolean mode has no threshold and no meaningful ranking, which is the trade — and boolean mode is what ends up shipping.

-- boolean mode: every term required, and no threshold
SELECT id, name,
       MATCH(name, description) AGAINST(? IN BOOLEAN MODE) AS score
FROM products
WHERE MATCH(name, description) AGAINST(? IN BOOLEAN MODE)
ORDER BY score DESC, sales_rank ASC
LIMIT 24;

-- the terms, built from user input:
--   'desk lamp' → '+desk* +lamp*'
-- and the escaping, which is not optional:
--   + - @ < > ( ) ~ * " are all operators

Escaping the operator characters is a security requirement rather than a correctness one — a user typing a bare - can exclude terms, and a malformed expression is a syntax error the database returns as a query failure. Building the boolean expression in one place with a test for each operator is a small function that is easy to get wrong.

The secondary sort on sales_rank is doing most of the work that relevance tuning would have done, because the boolean score is a term-frequency sum that ties constantly. That is a domain-specific tiebreak and it is better than the search engine’s default was.

The Scout database driver, and where it stops

// config/scout.php — a driver, not a rewrite
'driver' => env('SCOUT_DRIVER', 'database'),

// the model is unchanged from the Elasticsearch version,
// apart from the attributes that pick the strategy
#[SearchUsingFullText(['name', 'description'])]
#[SearchUsingPrefix(['sku'])]
public function searchableAs(): string { return 'products'; }

// without the attribute the driver generates LIKE %term%,
// which cannot use any index.

The interface being identical is what makes this reversible: SCOUT_DRIVER is an environment variable, so moving back is a deploy rather than a rewrite. That reversibility is most of the argument for doing it at all — the decision can be undone if the row count grows.

Without the attribute the driver generates LIKE %term%, which cannot use any index and is a full scan on every search. The attribute is the difference between a usable driver and an unusable one, and it is easy to miss because the unusable version works correctly on a development dataset.

Where the threshold actually is

measured on this data, not guessed:

  rows      p95 query   verdict
  40,000       11ms      comfortable
  200,000      28ms      fine
  1,000,000    94ms      acceptable, and the ceiling
  1,000,000 + facets     no

the row count is not the constraint. these are:

  faceted counts over a filtered set
  typo tolerance
  relevance that needs field weighting
  synonyms, stemming beyond the built-in
  more than about 4 concurrent searches per second

The concurrency limit is the one that is easy to miss: a full-text search competes for the same buffer pool and the same connections as everything else, where a search engine has its own machine. A search-heavy site is a different calculation from a catalogue with a search box, and the row count is a poor proxy for which one you have.

Verifying it worked

# the same queries, ranked, against both
$ ./bin/compare-search --queries=captured.txt --top=10
  identical top-10:      412 / 500
  same set, reordered:    71 / 500
  materially different:   17 / 500

# the 17 were reviewed by hand. 11 were better, 6 worse.
# the 6 were all short terms below the token size limit.

$ curl -s -o /dev/null -w '%{time_total}n' '/search?q=desk+lamp'
0.031        # was 0.024 via Elasticsearch

# and the operational change:
#   3 nodes, 12 GB heap, a CA, a runbook → none of it

Seventeen materially different results out of five hundred, reviewed by a person, is the acceptance criterion — and eleven of them being better than the search engine was a genuine surprise, caused by the sales-rank tiebreak that the Elasticsearch configuration had never had. The six worse ones were all short search terms and are the known limitation.

Seven milliseconds slower per query is the cost and is invisible next to a page that takes two hundred milliseconds. Reporting that honestly rather than claiming a speedup mattered, because the case for this change was never performance.

What this costs

A ceiling that will be reached, and a plan for it. The Scout interface makes the return journey a configuration change, which is the mitigation — but the queries written against boolean mode with a domain tiebreak do not translate back, so the return would be a rewrite of the ranking. Writing down what the ceiling is and what would signal reaching it is the part that makes this a decision rather than a bet.

The short-term problem is also permanent without a server restart. innodb_ft_min_token_size is a server-level setting that rebuilds every full-text index when changed, so a catalogue with two-character size codes has a search that will not find them and no per-index way to fix it. On a managed database that setting may not be available at all.