Full-text search and BM25

Why lexical search still matters

Lesson 02 searched by meaning. It's tempting to stop there, because if a model understands that "money back" means "refund", why bother matching words?

Because a great many real queries aren't about meaning: they're an error code, an order number, a SKU, a person's surname, a product name like "Acme Pro 2". Embed a bare token like 429 and you get a vector pointing nowhere in particular, since there's little semantic content to capture and the right document drifts down the ranking, whereas lexical search matches that token instantly and exactly.

This isn't the same as LIKE '%nakatomi%' you may have used before, either. A LIKE scan has no ranking, no notion of which documents are a better match, no stemming, and it reads every record, while a full-text index gives you all four.

The algorithm doing the ranking is BM25, and it has been the lexical-search default for about thirty years. It scores a document on three intuitions:

• •

  Term frequency: a document mentioning your term repeatedly is probably more about it. With diminishing returns, so a term repeated twenty times isn't twenty times better.

• •

  Rarity: a term appearing in nearly every document tells you nothing. A term appearing in one tells you a great deal.

• •

  Length: a short document containing your term is a tighter match than a long one that happens to include it.

Step 1 - start a server

We'll use the same 25 films as lesson 02, described in words this time and with no embeddings at all. Searching one corpus two different ways is what makes the contrast at the end of this lesson concrete.

surreal start --user root --pass secret

Step 2 - define an analyzer

Before anything can be indexed, raw text has to become tokens. An analyzer is that rule, and it runs on both sides: your documents when they're written, and your query when you search. That's why a search for "hunting" finds a document that says "hunts".

DEFINE ANALYZER OVERWRITE film_analyzer
    TOKENIZERS blank, class
    FILTERS lowercase, ascii, snowball(english);

SurrealDB's snowball filter ships with seventeen languages: Arabic, Danish, Dutch, English, French, German, Greek, Hungarian, Italian, Norwegian, Portuguese, Romanian, Russian, Spanish, Swedish, Tamil and Turkish. The language is part of the filter, so snowball(german) stems German and an analyzer can only stem one language at a time.

You can see the output. search::analyze runs the pipeline and hands back the tokens:

search::analyze("film_analyzer", "The SURVIVORS were hunting a café in Nakatomi-2");

[ 'the', 'survivor', 'were', 'hunt', 'a', 'cafe', 'in', 'nakatomi', '-', '2' ]

Every filter is visible in that one line: SURVIVORS lowercased and stemmed to survivor, hunting stemmed to hunt, café flattened to cafe, and Nakatomi-2 split into three tokens by the class tokenizer.

Stemming is cruder than it looks

Snowball is an algorithm that chops suffixes, not a dictionary that understands English. Related words can land in different buckets:

search::analyze("film_analyzer", "survive survives survived surviving survivor survivors");

[ 'surviv', 'surviv', 'surviv', 'surviv', 'survivor', 'survivor' ]

Four of those collapse to surviv and two to survivor, so a search for survive will not match a document that says survivors. That is the stemmer working as designed rather than the index failing.

Where that distinction matters, SurrealDB has a filter for it. mapper(path) does lemmatisation instead of stemming: you point it at a dictionary file mapping each surface form to its base form, and irregular verbs and awkward plurals land where they should. Lemmatisation files for most languages are easy to find, which makes this the usual route for languages Snowball never covered. The file is read from the host filesystem at DEFINE ANALYZER time and gated behind SURREAL_FILE_ALLOWLIST, so the directory has to be permitted before the analyzer will define.

For anything a filter can't express, DEFINE ANALYZER ... FUNCTION fn::my_preprocessor runs a SurrealQL function over the raw input before tokenizers and filters see it.

Step 3 - define the index

DEFINE INDEX OVERWRITE film_fts ON film
    FIELDS synopsis
    FULLTEXT ANALYZER film_analyzer BM25(1.2, 0.75) HIGHLIGHTS;

Three things to know:

• •

  BM25(1.2, 0.75) are the parameters k1 and b. k1 controls how quickly repeated terms stop adding score; b controls how hard long documents are penalised (0 = not at all, 1 = fully). These are the conventional defaults and a good starting point, and they should only be tuned with a test set in front of you.

• •

  HIGHLIGHTS stores token positions so search::highlight() and search::offsets() work. It's opt-in, and it isn't free; those positions are extra index data you're paying to store and maintain on every write. Use it if you will actually show users what matched, and leave it off otherwise. Lesson 05's index omits it, because that lesson only ever needs search::score(). And it fails quietly. Call search::highlight() on an index without HIGHLIGHTS and it returns the text unchanged; search::offsets() returns NONE. Nothing tells you why.

• •

  A full-text index covers exactly one field. Here that's synopsis. To search title and body, either concatenate them into one indexed field or define a second index and match against both.

Nothing in the file defines the film table itself, and nothing needs to: an undefined table is schemaless by default, so the seed's CREATE statements in Step 4 bring it into being, and the analyzer and index above are the only structure this lesson relies on.

Save the analyzer and index as schema.surql, starting the file with OPTION IMPORT, which surreal import requires. Then load it:

surreal import --endpoint http://localhost:8000 \
  --user root --pass secret --ns ai --db fulltext schema.surql

Step 4 - seed the films

CREATE film:die_hard SET title = "Die Hard", year = 1988,
    synopsis = "An off-duty cop fights terrorists who seize the Nakatomi Plaza tower at Christmas.";

CREATE film:groundhog SET title = "Groundhog Day", year = 1993,
    synopsis = "A weatherman repeats the same day over and over in a small town until the day means something.";
-- ...and twenty-three more, in full below

OPTION IMPORT;

CREATE film:mad_max SET title = "Mad Max: Fury Road", year = 2015,
    synopsis = "Survivors flee a warlord across a desert wasteland in an armoured war rig.";

CREATE film:john_wick SET title = "John Wick", year = 2014,
    synopsis = "A retired hitman returns to the underworld to hunt the men who killed his dog.";

CREATE film:die_hard SET title = "Die Hard", year = 1988,
    synopsis = "An off-duty cop fights terrorists who seize the Nakatomi Plaza tower at Christmas.";

CREATE film:the_raid SET title = "The Raid", year = 2011,
    synopsis = "A SWAT team is trapped in a tower block and must survive floor by floor.";

CREATE film:matrix SET title = "The Matrix", year = 1999,
    synopsis = "A hacker learns reality is a simulation and joins a rebellion against the machines.";

CREATE film:terminator2 SET title = "Terminator 2", year = 1991,
    synopsis = "A cyborg protects a boy from a shape-shifting assassin sent back by Skynet.";

CREATE film:edge SET title = "Edge of Tomorrow", year = 2014,
    synopsis = "A soldier relives the same battle every time he dies, learning the alien enemy move by move.";

CREATE film:aliens SET title = "Aliens", year = 1986,
    synopsis = "The survivor of a derelict spacecraft returns to the moon where the creatures were found.";

CREATE film:br2049 SET title = "Blade Runner 2049", year = 2017,
    synopsis = "A replicant blade runner hunts for Deckard and uncovers a secret about his own origin.";

CREATE film:arrival SET title = "Arrival", year = 2016,
    synopsis = "A linguist learns to speak with alien visitors and finds their language reshapes how she experiences time.";

CREATE film:interstellar SET title = "Interstellar", year = 2014,
    synopsis = "A pilot leaves a dying Earth through a wormhole to find a habitable planet for his children.";

CREATE film:solaris SET title = "Solaris", year = 1972,
    synopsis = "A psychologist sent to a space station finds the ocean planet below manifesting his dead wife.";

CREATE film:airplane SET title = "Airplane!", year = 1980,
    synopsis = "A traumatised pilot must land a passenger jet after the crew is poisoned.";

CREATE film:hangover SET title = "The Hangover", year = 2009,
    synopsis = "Three friends wake with no memory of the night before and a missing groom to find.";

CREATE film:groundhog SET title = "Groundhog Day", year = 1993,
    synopsis = "A weatherman repeats the same day over and over in a small town until the day means something.";

CREATE film:shaun SET title = "Shaun of the Dead", year = 2004,
    synopsis = "A slacker and his friend try to survive a zombie outbreak by hiding in their local pub.";

CREATE film:bttf SET title = "Back to the Future", year = 1985,
    synopsis = "A teenager travels thirty years into the past in a converted car and must repair the future.";

CREATE film:ghostbusters SET title = "Ghostbusters", year = 1984,
    synopsis = "Three parapsychologists start a business catching ghosts across the city.";

CREATE film:galaxy_quest SET title = "Galaxy Quest", year = 1999,
    synopsis = "The cast of a cancelled space show is recruited by real aliens who believe the episodes were history.";

CREATE film:godfather SET title = "The Godfather", year = 1972,
    synopsis = "The youngest son of a crime family takes over the business he swore he would never join.";

CREATE film:manchester SET title = "Manchester by the Sea", year = 2016,
    synopsis = "A janitor returns to his home town as guardian of his nephew and confronts an old grief.";

CREATE film:twelve_men SET title = "12 Angry Men", year = 1957,
    synopsis = "One juror refuses to convict and slowly argues the other eleven through the evidence.";

CREATE film:whiplash SET title = "Whiplash", year = 2014,
    synopsis = "A young drummer is pushed past breaking point by an abusive conservatory conductor.";

CREATE film:heat SET title = "Heat", year = 1995,
    synopsis = "A detective hunts a disciplined crew planning one last bank heist in Los Angeles.";

CREATE film:gladiator SET title = "Gladiator", year = 2000,
    synopsis = "A betrayed general fights his way through the arena to reach the emperor who murdered his family.";

surreal import --endpoint http://localhost:8000 \
  --user root --pass secret --ns ai --db fulltext seed.surql

Step 5 - match and score

To run the following script you'll need queries.surql.

surreal sql --endpoint ws://localhost:8000 \
  --user root --pass secret --ns ai --db fulltext --pretty < queries.surql

The @@ operator asks whether a full-text field matches. The number inside it is a label, not a setting: @1@ marks this match as number 1, and search::score(1) then reads back the BM25 relevance that match produced. On a query with a single @@ the number is arbitrary, and @1@ is just the conventional choice.

The labels start to matter once a query matches on more than one field. Give each @@ its own number and every match gets a score you can read separately:

-- Illustrative: this lesson's schema indexes synopsis only.
SELECT title,
    search::score(1) AS title_score,
    search::score(2) AS synopsis_score
FROM film
WHERE title @1@ 'hunt' OR synopsis @2@ 'hunt';

Separate scores mean you can weigh them against each other. A title match usually says more about a document than a body match, so counting it double is a matter of arithmetic in the projection:

SELECT title, (search::score(1) * 2) + search::score(2) AS score
FROM film
WHERE title @1@ 'hunt' OR synopsis @2@ 'hunt'
ORDER BY score DESC;

That's all it is: the numbers connect a @@ in the WHERE clause to a search::score() in the projection, and everything else is ordinary SurrealQL.

Where lexical search wins outright is a rare proper noun:

SELECT title, year, search::score(1) AS score
FROM film
WHERE synopsis @1@ 'nakatomi'
ORDER BY score DESC;

[
    { title: 'Die Hard', year: 1988, score: 2.82 }
]

One document matches, nothing else competes, and there is no threshold to tune. No embedding model will beat that, because there is nothing here to understand. nakatomi appears in exactly one synopsis and the index knows precisely where.

Step 6 - stemming, and seeing what matched

Because both sides run through the analyzer, one query term finds every surface form. search::highlight wraps the matches so you can see it happening:

SELECT title, search::score(1) AS score, search::highlight('**', '**', 1) AS matched
FROM film
WHERE synopsis @1@ 'hunt'
ORDER BY score DESC;

[
    { title: 'Heat',              score: 1.93, matched: 'A detective **hunts** a disciplined crew planning one last bank heist in Los Angeles.' },
    { title: 'John Wick',         score: 1.88, matched: 'A retired hitman returns to the underworld to **hunt** the men who killed his dog.' },
    { title: 'Blade Runner 2049', score: 1.88, matched: 'A replicant blade runner **hunts** for Deckard and uncovers a secret about his own origin.' }
]

One query term matched two surface forms, hunt and hunts.

All three synopses contain the term exactly once, so term frequency can't separate them (yet Heat comes out ahead). That's the length normalisation b controls: Heat's synopsis is 15 tokens, the other two are 16 each. Note that John Wick and Blade Runner 2049 score identically despite their synopses differing in length by eight characters, because BM25 counts tokens rather than characters.

Step 7 - what BM25 actually rewards

The scoring is easiest to see at the two extremes.

Rare and repeated. The token day appears twice in Groundhog Day's synopsis and in no other film:

SELECT title, search::score(1) AS score FROM film WHERE synopsis @1@ 'day' ORDER BY score DESC;

[
    { title: 'Groundhog Day', score: 3.43 }
]

3.43: the highest score anywhere in this lesson. Rare across the corpus and frequent within the document is what BM25 is looking for.

Too common to be useful. At the other extreme, the token a appears in 23 of the 25 synopses:

SELECT title, search::score(1) AS score FROM film WHERE synopsis @1@ 'a' ORDER BY score DESC LIMIT 6;

[
    { title: 'Mad Max: Fury Road', score: 0 },
    { title: 'John Wick',          score: 0 },
    { title: 'The Raid',           score: 0 },
    { title: 'The Matrix',         score: 0 },
    { title: 'Terminator 2',       score: 0 },
    { title: 'Edge of Tomorrow',   score: 0 }
]

Every document scores exactly zero, because a term that nearly all of them contain cannot discriminate between them, and BM25's inverse-document-frequency term collapses to nothing. This is why you rarely need a stop-word list: the maths already ignores words that carry no information.

Step 8 - matching any term

By default @@ requires all of the terms, while ,OR@ lets any one of them do - the way a search box behaves:

SELECT title, search::score(1) AS score
FROM film
WHERE synopsis @1,OR@ 'hunt alien'
ORDER BY score DESC;

[
    { title: 'Heat',              score: 1.93 },
    { title: 'John Wick',         score: 1.88 },
    { title: 'Blade Runner 2049', score: 1.88 },
    { title: 'Arrival',           score: 1.79 },
    { title: 'Edge of Tomorrow',  score: 1.75 },
    { title: 'Galaxy Quest',      score: 1.75 }
]

Six films, from two query terms: three matched hunt, three matched alien. Nothing matched both, so nothing gets promoted for it.

Step 9 - the blind spot

And here's where it fails. There are two time loop films in this corpus, Groundhog Day and Edge of Tomorrow, and any human would name Groundhog Day first:

SELECT title, search::score(1) AS score
FROM film
WHERE synopsis @1,OR@ 'time loop'
ORDER BY score DESC;

[
    { title: 'Arrival',          score: 2.16 },
    { title: 'Edge of Tomorrow', score: 2.11 }
]

Two things went wrong at once.

The top result is wrong. Arrival isn't a time-loop film: its synopsis merely contains the word "time" ("reshapes how she experiences time"). BM25 matched the token and had no way to know the sense was different.

The right answer is missing entirely. Groundhog Day, the definitive time-loop film, doesn't appear at all - because its synopsis says "repeats the same day over and over" and never uses the word "time". Zero overlapping tokens, so as far as the index is concerned it isn't a match at all. It hasn't been ranked low, it is simply absent from the results.

No amount of BM25 tuning fixes this. k1 and b adjust how matches are weighted; they cannot manufacture a match that shares no words. Vector search doesn't fail this way: an embedding of "time loop" lands near "repeats the same day over and over" because they mean the same thing.

Where this goes next

So those are the two methods, and they fail in opposite ways:

Neither is enough on its own, and a real user question usually has both kinds of signal: an intent and a specific term. Lesson 04 builds a knowledge base on the semantic half, and lesson 05 runs both retrievers over one table and fuses their rankings, so a query like "refund for order 8823" is covered from both sides.