My timer application’s search was supposed to favor title matches. The query supplied two weights, but the search table had four columns. Both weights landed on unindexed metadata.
This was the relevant part of the query:
ORDER BY bm25(search_idx, 2.0, 1.0)The intended meaning was a weight of 2.0 for the title and 1.0 for the body. Search still returned results, and those results were ranked. The missing behavior was the extra weight for title matches.
The weights follow the table’s column order
SQLite’s FTS5 full-text search extension provides bm25() for ranking matches. Its weight arguments correspond to columns by position, starting with the leftmost column in the table definition.
That includes columns marked UNINDEXED. They don’t contribute searchable terms, but they still occupy positions in the table.
Here’s the schema:
CREATE VIRTUAL TABLE search_idx USING fts5(
entity_type UNINDEXED,
entity_id UNINDEXED,
title,
body,
tokenize = 'porter unicode61'
);The first weight belonged to entity_type, and the second belonged to entity_id. Neither could affect the relevance score because neither column was indexed.
There were no supplied weights left for title or body. SQLite assigns omitted weights a default of 1.0, so both searchable columns received the same weight.
The query was valid. SQLite had no reason to reject it, and it couldn’t know that the two numbers were intended for different columns. Increasing the first number would only change the weight assigned to entity_type; it still wouldn’t boost the title.
The corrected query includes the metadata positions:
ORDER BY bm25(search_idx, 0.0, 0.0, 2.0, 1.0)The zeros make those unused positions explicit. The title and body weights now line up with the third and fourth columns.
I like keeping the full list here. It makes the relationship to the schema visible, which matters if someone later adds or reorders columns.
A weight isn’t a final-score multiplier
There is a separate detail worth knowing before tuning that title weight.
BM25 accounts for how often a search term appears, but repeated occurrences have diminishing returns. The column weight changes the term frequency before that saturation step. Doubling the weight therefore doesn’t double the final score.
Document length and the other matches also affect the result. A title weight of 2.0 favors title matches relative to the same occurrences in the body; it doesn’t promise that every title-only match will outrank every body-only match.
That’s enough context for this fix. The immediate problem wasn’t choosing the best weight. It was getting the configured weight onto the intended column.
Test the comparison
A regression test needs to isolate that behavior. Use two documents of equal indexed length, with the same query term appearing once in each. Put the match in one document’s title and the other’s body.
Then check that the title match has a better score and appears first. In SQLite FTS5, a better BM25 match has a numerically lower score, so the ascending order in the query is intentional.
Checking the score relationship matters as well as the returned order. With the broken weights, otherwise equivalent documents can tie, and an incidental tie order could make an ordering-only assertion pass.
This isn’t a complete test of search quality. It’s a focused test of the title boost, with the other factors controlled so they don’t obscure the behavior being checked.
A test that checks whether search returns results won’t catch a missing title boost. For that, it needs to check which result comes first and why.
Sources
- SQLite FTS5 documentation — positional weights, default values, and BM25 scoring
I’d appreciate a follow. You can subscribe with your email below. The emails go out once a week, or you can find me on Mastodon at @[email protected].