SQLite FTS5 ranking: what bm25 scores mean, and three reasons results come back in the wrong order

SQLite FTS5 ranking works the same way every time: each row gets a bm25 score, and the best match has the lowest, most negative number. That is why ORDER BY rank, ascending, puts the best row first. Below we explain the formula, then show the three surprises that make results look wrong. Tested on SQLite 3.53.2 on October 28, 2026.

How sqlite fts5 ranking scores a row

FTS5 scores a row with the standard Okapi bm25 formula. Per the SQLite FTS5 documentation, each query phrase contributes an inverse-document-frequency term (how rare the phrase is across the table) multiplied by a term-frequency term that is dampened by the length of the row. The constants are hard-coded: k1 is 1.2 and b is 0.75. You cannot change them.

The score is then multiplied by -1. The docs explain why: without it, a better match gets a higher number, and the default ascending sort would return the worst rows first. FTS5 flips the sign so that "ORDER BY bm25(ft)" does what it looks like it does. If you want a positive score for display, negate it in your application code.

Two details in the source matter later. The length |D| is the token count of the whole row across every column, and it is never weighted. And the phrase frequency is the number of hits, optionally multiplied by a weight per column.

The hidden "rank" column holds the same value as bm25() with no arguments. The docs on sorting say sorting by rank is faster than calling bm25() in the ORDER BY, particularly with a LIMIT. Outside a MATCH query, rank is NULL; we confirmed that.

Reproduce it: twelve rows and a hand-calculated score

You can check all of this in a few minutes with Python's built-in sqlite3 module (we used Python 3.14.5, SQLite 3.53.2). Create a table with CREATE VIRTUAL TABLE doc USING fts5(title, body), then insert twelve rows. The rows that matter are in the table below; the other six are filler about cron, indexes, migrations, query plans, tokenizers and C++ builds, and never contain the words "backup" or "search".

Search for "backup" with SELECT title, rank FROM doc WHERE doc MATCH 'backup' ORDER BY rank. The table has N = 12 rows, the average row length is 10.5 tokens, and four rows contain the term, so IDF = ln((12 - 4 + 0.5) / (4 + 0.5)) = 0.6360. We then computed each score by hand from the documented formula and compared it with what SQLite returned:

They match to six decimal places. Two things to read from it. A two-token row with two hits beats everything because length normalization rewards short rows. And "sqlite-backup" counts as a hit for "backup", because the default tokenizer splits on the hyphen.

Why a stuffed row can beat your best title match

Search for "search" in the same table. The default order puts "Weights" first (-2.2437) and "Search ranking" second (-1.8494). Both rows contain the word twice, but "Weights" has five tokens in total and "Search ranking" has thirteen, so the shorter row wins. A row whose title is the thing you searched for loses to a short row that repeats it.

Column weights fix that. Pass one weight per column after the table name, as the docs show: bm25(doc, 10.0, 1.0) counts a hit in the title as ten hits in the body. With that call, "Search ranking" moves to first place (-2.7975) and "Weights" keeps its score (-2.2437), because both of its hits are in the body.

Three details decide whether this works for you:

• Weights multiply the hit count, not the row length. In the source the weight is added to the phrase frequency, while the token count |D| stays unweighted.
• Weights are positional, and an UNINDEXED column counts as a position. On a table declared fts5(slug UNINDEXED, title, body), we found that bm25(p, 10.0) puts the weight on the slug and changes nothing, and it takes bm25(p, 0.0, 10.0, 1.0) to weight the title.
• Weights can be made the default. The rank configuration option takes the same text, so INSERT INTO doc(doc, rank) VALUES('rank', 'bm25(10.0, 1.0)') makes plain ORDER BY rank use title weighting. We confirmed that it reorders results.

Why every fts5 score looks the same: the 1e-06 floor

If every row in your results scores -1e-06, the cause is not a bug. A term that appears in half the rows or more would have an IDF of zero or less in the textbook formula. The comment in fts5_aux.c says negative IDF is "undesirable", so the minimum allowed value is 1e-6, and the code reads "if( idf<=0.0 ) idf = 1e-6;". We checked it on a four-row table:

Note the boundary: exactly half the rows is already floored, because the IDF is zero there. Rows still sort by tiny differences, so the order is stable but carries almost no relevance signal.

This bites in two places. Unit tests with a handful of fixture rows, where almost every term is in half the rows, will tell you nothing about ranking. And in a multi-word query, a common word such as "the" contributes almost nothing to the score, which is usually what you want. In our view, test ranking on a copy of real data, not on fixtures.

Why raw user input throws syntax errors, and how to quote it

The text you pass to MATCH is a query language, not a search box. The docs on strings allow unquoted barewords made only of letters, digits, underscore and non-ASCII characters; strings with anything else must be quoted, and "any embedded double quote characters may be escaped SQL-style - by adding a second double-quote character." The docs also warn that characters not allowed in barewords may get a meaning in a future version.

Here is what we got from raw input in our test: "sqlite-backup" failed with "no such column: backup", "what?" failed with a syntax error near "?", and "c++" failed with a syntax error near "+". The fix is to build the query yourself: split the user's text on whitespace, double any double quote inside each piece, wrap each piece in double quotes, join with spaces (an implicit AND), and pass the result as a bound parameter. After that, "sqlite-backup" matched the cheat-sheet row, "what?" matched the C++ build row, and "c++" matched the same row with no errors.

Two catches. Quoting turns AND, OR, NOT and column filters into plain words (we checked that a quoted "AND" matches the literal word), so users lose operators; that is a fair trade for a public search box. And a star inside the quotes is not a prefix operator, because the docs on prefix queries say it is passed to the tokenizer. For search-as-you-type, put the star after the closing quote: "back"* matched all four backup rows, while "back*" matched only the row that contains the separate word "back".

Tokenizers decide what can match at all

Ranking only orders rows that matched, and the tokenizer decides which rows match. We ran three queries against three tokenizer settings on four rows ("running shoes", "runs fast", "sqlite-backup tool", "Café menu"):

The default unicode61 tokenizer removes diacritics and is case-insensitive, but does no stemming. Adding porter gives stemming. The trigram tokenizer matches any substring, but the docs say substrings under three characters match nothing and diacritics are removed only if you set remove_diacritics, which our "cafe" result is consistent with.

When SQLite FTS5 stops being enough

FTS5 gives you one scoring function with fixed constants, column weights and a choice of tokenizer. In our view that is plenty for documentation search, a notes app or an admin panel. Skip it if you need typo tolerance, synonyms, faceting or ranking that learns from clicks; in our view that is the point to look at a search engine. As of October 28, 2026, Upfynd lists 99 tools in Search & Discovery, including Typesense, OpenSearch, Elasticsearch and the hosted Algolia. Check each one's own docs for how it scores, since we have not compared them here.

If your SQLite file is the only copy of your index, protect it first: see our guide to SQLite backup to S3 with Litestream. More infrastructure options are in Databases & Infrastructure, which lists 247 products, and if you build a search tool, you can submit it for a free listing.

What we did not test

We ran one small table on one version, SQLite 3.53.2, and read the docs and the master-branch source on October 28, 2026. SQLite's behavior can change between versions, so rerun the checks on your own build. We did not test large tables, FTS5's other auxiliary functions (highlight and snippet), or custom ranking functions.