Building Enterprise Search on MySQL FULLTEXT: Architecture, Evaluation and What Broke

We rebuilt the site search for a market-research publisher with a catalogue of about 24,000 long-form reports, and we did it without adding a search cluster. The engine is MySQL 8 FULLTEXT in boolean mode, wrapped in a small separate service, with every ranking decision tuned to the catalogue's own vocabulary and every change gated by an evaluation harness of roughly 7,100 generated queries. This is the engineering write-up: the architecture, the query pipeline stage by stage, how we evaluated it, the cutover, and the things that broke after launch.

The business side of the same work (why failed search costs money, how to measure success, how to build a fair test set) is written up on itmtb.com. Links are at the relevant points below and collected at the end.

What was wrong with the old search?

The old search was a substring scan on titles, and its failures were invisible.

It had two paths. The header type-ahead ran title LIKE '%q%', plus a SOUNDEX comparison for single words. The results page ran the same LIKE '%token%' per token. Three properties followed from that design:

• Title only. A report about a market whose title did not contain the visitor's exact words could not be found, however relevant its contents were.
• A full table scan on every search. A leading wildcard defeats every index. Warm, it looked fast. Cold, a results-page search took about 6.9 seconds on the test environment.
• Errors looked like zero results. A failed query returned an empty list and wrote no telemetry, so "the search broke" and "we have nothing on that" were indistinguishable.

There was no click tracking and only a small rotating log of keystrokes. Nobody could answer "what are people searching for and not finding?" without reading log files by hand.

A typo like "quantam computing" returned nothing at all. On a sample of real searches, more than half returned no results, and most of those failures were retrieval failures: the catalogue had a good answer that the search could not reach. That analysis, and why it matters commercially, is in Improving Enterprise Search for People and AI Agents and Why Enterprise Search Is Important. Those figures come from a sample of logged searches, and the corrected figures there are projections from a 500-query sample, labelled as such.

Why keep MySQL FULLTEXT instead of a search cluster?

Because the measured quality was achieved on it, and moving engines would mean re-deriving the ranking rather than translating it.

The engine started life as a prototype for agent access to the catalogue. On a held-out set it found the intended report in the top five for 98.24% of queries. Every ranking constant in it (title weight, body weight, boosts, the spelling cutoff) is denominated in MySQL FULLTEXT's scoring units. Porting to OpenSearch, SQLite FTS5 or an in-memory index is possible, but it is a re-tuning project with its own evaluation, not a deployment detail. We wrote that down as a decision: a port is allowed only behind a differential evaluation against the current engine.

What we did not do was add a FULLTEXT index to the live product table. That table takes writes from the publishing workflow, and adding the index would have meant a rebuild under a write lock on a shared production database. Instead the service keeps its own index table in a separate schema:

report_search_index
  content_id, slug, title, summary (display only), industry_name,
  published_at, price, search_blob (MEDIUMTEXT), indexed_at, tenant_id
  FULLTEXT ft_title (title)
  FULLTEXT ft_blob  (search_blob)

search_blob flattens the title, industry, segmentation and key-company text into one searchable field. A FULLTEXT index on the product table's title and summary could never have reached the segmentation text, which is where many topical queries actually match.

We also left innodb_ft_min_token_size at its default of 3. Lowering it would have required a database restart and a full reindex on a shared instance. The cost is that two-letter terms such as AI and EV are not indexed, and the pipeline has to work around that (see the acronym and short-term stages below).

OpenSearch is deferred, not rejected. If faceted search on the new engine becomes a requirement, the trade changes.

Why does search run as a separate service?

Because the main application's connection pool had already taken production down once, and search is the highest-volume read path on the site.

We considered running the engine as a library inside the existing backend. We rejected it for five reasons:

1. Dependency conflict. The backend was pinned to an older FastAPI than the engine needed.
2. Crash isolation. An engine bug would take down checkout and enquiries with it.
3. Shared database pool. The backend's pool is the thing that had already wedged production.
4. Shared thread pool. A slow search would queue behind, or in front of, everything else.
5. Coupled deploys. Every ranking change would be a full backend release.

A remote service behind its own load balancer was also rejected: it adds a fleet and a public surface for no benefit at this scale. The service runs on the same host as the backend, bound to loopback only, with its own repository, virtual environment, process supervisor unit and release pipeline.

visitor -> CDN / WAF -> load balancer -> backend
                                           |
                                 loopback HTTP (timeout)
                                           |
                                    search service  --(capped DB user, 10 conns)-->  search schema
                                           |
                                  on any failure: backend falls back to legacy search

The pool lesson came from an earlier production incident: a slow listing query, combined with a release that raised the backend's pool from 70 to 220 connections, let about 220 slow queries pile onto MySQL at once, and each went from roughly 2 seconds to roughly 900. The line we took from that RCA is a large connection pool adds no throughput; it removes back-pressure. So the search service's database user is capped at 10 connections at the database level, and its own pool is 5 plus 5 overflow. A search storm can saturate search. It cannot take the database away from orders.

The routes are synchronous handlers on FastAPI's thread pool, deliberately. The prototype used async handlers around a synchronous MySQL driver, which serialised work and capped it at about 20 searches per second. After the change, 20 concurrent searches complete in 41 ms of wall time, and one process sustains 583 requests per second on a local benchmark.

The query pipeline, stage by stage

Every stage exists because a measured failure needed it. In order:

1. Tokenise safely. Tokens are alphanumeric runs only. In boolean mode + and - are operators, so a naive +wi-fi* means "require wi, exclude fi".
2. Expand acronyms. Known acronyms of three or more letters are expanded additively, so both the short and long forms can match.
3. Drop catalogue stopwords. Beyond the standard list, words that appear in a large share of titles ("market", "report") carry no subject, and are removed using the catalogue's own frequencies.
4. Reduce plurals against the vocabulary. A plural is reduced only if the singular exists in the index vocabulary.
5. Demote scaffold words. Scope words, geography and four-digit years may help rank a result, but are never required. More on this below; it was the single biggest fix.
6. Boost short terms and compounds. Two-letter terms found in a title get a boost of 20. Hyphenated compounds with a short side (the X-ray shape) get a boost of 100 through a title pattern match, because the tokeniser would otherwise split them into fragments.
7. Relax, with guards. If the strict query finds nothing, require any k of the n terms, subject to three limits described below.
8. Reject weak matches. If fewer than half the query's terms matched, the result is discarded. This is the guard that stops a nonsense query from being "rescued" into a confident wrong answer.
9. Correct spelling against the catalogue. Described in its own section.
10. Promote exact titles. A report whose title reduces to the same subject as the query, once convention words are stripped from both, goes first.
11. Rank.
12. Apply business rules, then optionally the semantic rescue.

The ranking expression is plain SQL:

relevance =
    10  x MATCH(title)       AGAINST(all terms required)
  +  3  x MATCH(title)       AGAINST(any term)
  + 0.3 x MATCH(search_blob) AGAINST(any term)
  + 300 / max(length(title), 20)        (only when the title matched)
  + short-term and compound boosts

ORDER BY relevance DESC, published_at DESC

The length term favours a short title that matches over a long one that happens to contain the same words. Business rules (pin up to three reports, exclude, or redirect a query) are matched on the normalised query and applied after the engine, from an operator console, with a preview and without a deployment.

Spelling correction against the catalogue, not a dictionary

A dictionary spell-checker corrects specialist vocabulary into common words. A research catalogue is full of terms a dictionary does not know, and "correcting" them destroys the query.

So the corrector's vocabulary is the catalogue's own title words, about 11,000 of them. A misspelt token is matched through several channels: a phonetic key, character-bigram similarity, a first-letter and length window, and a consonant skeleton (added after launch, because vowel swaps such as "aparel" scored below the cutoff on bigrams alone). Candidates need a similarity of at least 0.78.

Two details mattered more than the matching itself:

• Rank candidates by co-occurrence first, edit distance second. In "micro rboots", both "robots" and "boots" are close. "Robots" wins because it co-occurs with "micro" in real titles.
• Correct when coverage is partial, not only when results are zero. A typo that leaves other words matching can still return results, just the wrong ones. Correction fires whenever a token occurs nowhere in the index verbatim.

The type-ahead has one exception: the word currently being typed is exempt from correction while it is still a valid prefix of some vocabulary word, so the corrector does not fight the visitor mid-word.

Relaxation: making longer queries help instead of hurt

On a strict AND query, every extra word is another chance to miss. Visitors type the house title convention back at the search box, add a geography, add a year, and get nothing.

The measured cause was specific. Scope words, geographies and years were being required, and no title contained all of them at once. That one mechanism was 46% of all measured failures. Demoting them to "may rank, never required" fixed most of it.

The relaxation step itself needed guarding. The first version dropped to the single rarest word when the strict query failed. That produces confident nonsense: a four-word query about a sensor returned a report about ships, because one rare word happened to match. The current version requires any k of n terms with three limits:

• Anchor: a term is kept mandatory in every relaxed pass only if it is at least ten times rarer than every other term in the query. Otherwise nothing is pinned. Always pinning the rarest word was exactly what produced the ships answer.
• Majority: a relaxed pass must keep more than half the terms (two-word queries excepted).
• Title evidence: the top five results must carry a subject word in their titles. If they do not, the honest answer is zero results.

Semantic rescue, and why it is not allowed to re-rank

There is an optional semantic channel: a small local sentence-embedding model (all-MiniLM-L6-v2 through ONNX, no external API) over precomputed report vectors. It has one job. It runs only when lexical search returns nothing.

It is gated in two tiers. A top hit with cosine similarity of at least 0.45 is accepted on its own. A hit between 0.35 and 0.45 is accepted only if a strict majority of the query's words exist in the catalogue vocabulary. The gate exists because embeddings will always return a nearest neighbour, including for a query like "purple flying spaghetti monster". On the nonsense test set, the gated channel leaks nothing.

The design rule we wrote down: semantic results may be appended, never used to re-rank lexical answers. Lexical ranking is what the evaluation measures. A semantic re-ranker would change the ordering in ways the harness does not cover, and the most visible failure mode of a catalogue search is the wrong report in first position. Retrieval-system readers will recognise the same separation from RAG architecture: keep the stages separately measurable, or you cannot tell which one failed.

How do you evaluate a search engine before it ships?

Generate queries from the catalogue itself, in the shapes real visitors produce, and require the intended report in the top five.

The harness samples report titles with a deterministic stride, mutates each one in 20 classes, and checks whether the source report comes back in the top five and the top ten. One run is about 7,100 queries and takes about 20 seconds, so it runs on every change. The classes:

Group	Classes
Exact	exact title, subject only, lowercase
Typos	delete, insert, substitute, transpose, plural noise, phonetic, two-word typo
Order	reverse, rotate, shuffle
Shape	dropped word, natural phrasing, punctuation stripped, hyphen joined, plural flipped, order plus typo, abbreviation

A second set of classes is derived from what visitors actually typed after launch: the house title convention, vowel swaps, extra words, exact subject, short compounds, and geography plus year. These are reported separately, not folded into the overall score, so that a field-derived fix cannot hide a regression elsewhere.

Results, all measured offline on the live catalogue snapshot:

• The prototype scored 98.24% top-5 on a held-out set of 28,974 queries (mean of seven seeds 98.30%, standard deviation 0.53).
• Lifting it into the production service was recall-neutral: 98.6% and 97.7% on two seeds.
• The first post-launch ranking release took those to 98.8% and 98.3%, with no class going down.

The field-derived classes moved much further, because they were the failures the generic classes did not model:

Class (seed 1)	Before	After
Title convention, top-1	61.8%	96.0%
Extra word, top-5	29.6%	73.3%
Geography plus year, top-5	13.1%	99.6%

Three rules make the harness useful rather than decorative:

• A class that drops while the overall number holds is a regression. Averages hide trade-offs.
• Every search event is stamped with the engine version, so live behaviour can be attributed to a release.
• Field failures become committed test cases. A query corpus of real complaints (46 checks at the last release) runs alongside the generated classes.

We also chased a run-to-run flicker of about 0.1 points for longer than we should have. The cause was ties: the ORDER BY has no final unique key, so after an index rebuild, equal-score rows can swap. The fix (a stable tiebreak) is on the open list.

How to construct a fair comparison set for search products in general is written up in A Realistic Dataset for Comparing Enterprise Search Solutions, and the same discipline for LLM and RAG systems is in Evaluating LLM, RAG and Agent Systems.

Telemetry that cannot take the database down

The old search had no telemetry because writing a row per search was considered too risky. That instinct was right about the mechanism and wrong about the conclusion.

The service writes one row per settled search, through a buffered writer that flushes every 3 seconds on one connection and collapses bursts within 5 seconds. If the buffer cannot write, a drop counter increments; no search ever opens its own connection to log itself. Errors are rows with an error status, so a broken search is visible as broken. Raw events are purged after 90 days; daily rollups are kept.

Clicks come from the browser through navigator.sendBeacon, carrying the search event id, the report id, the position and the surface. The backend forwards them fire-and-forget. A click whose event id does not exist never joins, which doubles as a cheap bot filter.

Counting turned out to be the subtle part. The first console counted searches by (query, visitor IP, 10-minute window). On day one it double-counted roughly 55 of the first 201 events: the type-ahead request and the results-page request for the same search reached the API over IPv4 and IPv6 respectively, so the same visitor looked like two. The intent key is now (query, 10-minute window). The trade-off is that two different visitors searching the identical query in the same window count once. A per-visitor session id would fix it exactly, and the column exists; the site does not send one yet.

What to do with these numbers once you have them (which measures matter, and how they relate to business outcome) is covered in How to Measure the Success of an Enterprise Search Tool.

Cutover: fallback first, and the shadow week we skipped

The backend has three modes for the new engine: off, shadow and live. In live mode, any failure (timeout, error, service down) returns the legacy result, and the response carries an engine field saying which engine answered. Fallback was measured at 0.18 seconds. Failure alerts are storm-suppressed to one email per 15 minutes; 50 consecutive fallbacks produce one email, not 50.

The plan was off, then a week in shadow comparing answers, then live. We went straight to live. The evaluation results were strong, the fallback was tested, and a week of shadow traffic at this volume would have told us less than a week of real clicks. It was a deliberate call, not an accident, and the rollback is one configuration value. I would make the same call again for a read-only path with a tested fallback. I would not make it for anything that writes.

Scope was also deliberate. Only the header type-ahead and "pure" results-page searches (no facet, no sort) go to the new engine; it returns ids and the backend hydrates them by primary key. Faceted and sorted searches, and two internal tools that share the old search function, stay on the legacy path until there is a reason to move them.

What broke after launch

None of these were caught by the offline harness, which is the point of listing them.

• Everything showed as degraded. Error counters were process-lifetime, so one bad request from a bot marked every later search as degraded. Status is now request-scoped and the health endpoint is windowed.
• Typos silently stopped correcting. The corrector's vocabulary was loaded at process start. The scheduled index rebuild refreshed the index but not the running vocabulary. The rebuild now calls an internal reload endpoint.
• The index job evicted the embedding matrix every 15 minutes, so the semantic channel kept reloading. Fixed in the latest release by evicting only after a newer build exists.
• The first acceptance feedback was four queries, all ranking problems: no exact-title signal, the house title convention overpowering the title boost, a vowel-swap typo below the spelling cutoff, and a hyphenated compound reduced to a prefix that matched thousands of reports. Each became a test case and a fix.
• One is still open. A prefix match on a short word can pull in an unrelated long word that starts the same way. We trialled three fixes. One was evaluation-neutral but hurt dropped-word queries; one dropped abbreviation recall to the 80s. None shipped. A fix that trades one class for another is not a fix.

How fast is it?

Fast enough that the database is the cost, not the code.

• Engine time: about 10 ms for a clean query, of which 97% is database time; about 26 ms for a query that needs spelling correction.
• First day in production, on a sample of 128 timed type-ahead requests: p50 23 ms, p95 247 ms, p99 1,849 ms. The tail is real and is on the list; at that sample size it is a few requests, not a distribution to optimise against yet.
• End to end through the public URL at cutover: 123 to 209 ms.
• A broad compound query that took 8.5 seconds on the test environment through the old fallback path takes about a quarter of a second after the compound-query path was rewritten.

What is still open

• A stable final tiebreak in the ORDER BY, and a re-baselined evaluation once it lands.
• A relevance floor constant exists in the code and is never applied. It should be wired or deleted.
• Click-based ranking is designed and deliberately not built. The plan is to measure click-through and click position for a few weeks first, then add a bounded boost (a minimum click count, decay, never above an exact title, never overriding a pinned rule).
• The engine exposes a read-only MCP endpoint (search_catalog, get_item, find_related) so an AI assistant gets the same answers as the search box. It is built and tested, and not enabled for this deployment.
• Facets, sort and deep paging still run on the legacy path.

FAQ

Do you need Elasticsearch or OpenSearch for enterprise search?

Not for a catalogue of tens of thousands of documents. We reached 98.8% top-5 on a 20-class evaluation with MySQL 8 FULLTEXT in boolean mode, a separate index table and a tuned query pipeline. A dedicated search cluster becomes worth its operating cost when you need facets, very large corpora or heavy write-and-search concurrency, and moving to one is a re-tuning project, not a port.

How do you make site search tolerate typos without a dictionary?

Build the spelling vocabulary from your own corpus, so specialist terms are never corrected into common words. Match candidates through several channels (phonetic, character bigrams, consonant skeleton), rank them by co-occurrence with the other query words before edit distance, and correct whenever a token appears nowhere in the index, not only when the result set is empty.

Why do longer search queries return fewer results, and how do you fix it?

Because a strict AND query requires every word, and visitors add words such as scope terms, geographies and years that no single title contains. Demote those words so they can rank results but never be required, then relax to any k of n terms with guards: pin a word only when it is far rarer than the rest, never relax to half the terms or fewer, and require subject evidence in the top titles.

How do you evaluate a search engine before launch?

Sample real titles from the corpus, mutate them into the query shapes visitors produce (typos, reordering, dropped and extra words, abbreviations, natural phrasing), and require the source document in the top five. Track every class separately on every change, treat a class drop as a regression even when the overall score holds, and add classes from live failures after launch.

Should semantic search replace keyword search on a catalogue?

Not as the primary ranker. We use a small local embedding model only when lexical search returns nothing, behind a two-tier similarity gate, and its results may be appended but never used to re-rank lexical answers. That keeps the measured ranking intact and stops nonsense queries from being answered with a confident nearest neighbour.

How do you log every search without overloading the database?

Write through a buffered writer on a single connection, flushing every few seconds, with a drop counter instead of a per-search connection. Record errors as rows so failures are visible, purge raw events on a schedule and keep daily rollups. Cap the service's database user so that no search load can starve the rest of the application.

---

Related reading

On aakashx:

• RAG Architecture: The Full Pipeline and Where Each Stage Fails, the same retrieval problems when the consumer is a language model.
• Evaluating LLM, RAG and Agent Systems, the evaluation discipline behind the harness described here.
• MCP Architecture and the Enterprise Tool Gateway, for exposing a search like this one to agents safely.

On itmtb.com, the business side of the same work:

• Enterprise search at ITMTB, what the engine and console do.
• Improving Enterprise Search for People and AI Agents, the zero-result analysis.
• Why Enterprise Search Is Important, what failed search costs.
• How to Measure the Success of an Enterprise Search Tool, the measures.
• A Realistic Dataset for Comparing Enterprise Search Solutions, the test-set method.