Relevance scoring

Relevance scoring lets you rank search results by how closely each record matches your query. When a query includes full-text search conditions such as MATCH_ANY or MATCH_ALL, SelectDB computes a numeric score for each matching record and returns it alongside the record. Sort by score descending to surface the most relevant results first.

SelectDB uses the Best Matching 25 (BM25) algorithm to calculate relevance scores.

How BM25 scoring works

BM25 is a probabilistic text relevance algorithm that scores each record based on three factors: term frequency, inverse document frequency (IDF), and record length. It handles long and short records more fairly than TF-IDF by normalizing for record length, and its tuning parameters let you adjust scoring behavior without rewriting queries.

Score formula

score = IDF × (tf × (k1 + 1)) / (tf + k1 × (1 - b + b × |d| / avgdl))

Variable	Description
`tf`	Frequency of the search term in the current record
`IDF`	Inverse Document Frequency — measures how rare the term is across all records
`\|d\|`	Length of the current record, measured in tokens after tokenization
`avgdl`	Average length of all records in the table
`k1`, `b`	Tuning parameters

The IDF and average document length are computed as:

IDF = log(1 + (N - n + 0.5) / (n + 0.5))
avgdl = total_terms / total_rows

Where N is the total number of records in the table and n is the number of records that contain the term.

For multi-term queries, the final score is the sum of the per-term scores.

Tuning parameters

Parameter	Default	Effect
`k1`	1.2	Controls term frequency saturation. Higher values let additional occurrences of a term keep increasing the score; lower values cause the score to plateau faster as term frequency rises.
`b`	0.75	Controls how much record length affects the score. A value of `1.0` fully normalizes for length; `0.0` disables length normalization entirely.
`boost`	1.0	A query-level weight multiplier applied to the entire score.

Use scoring in a query

Supported index and query types

Score calculation requires a tokenized inverted index. A non-tokenized inverted index supports only exact matches and does not produce scores.

The following query types return scores when used with a tokenized inverted index:

MATCH_ANY
MATCH_ALL
MATCH_PHRASE
MATCH_PHRASE_PREFIX
SEARCH

Requirements for score computation

Score computation is only triggered when the query is structured as a Top-N retrieval. All three of the following conditions must be met:

The SELECT clause includes the score() function.
The WHERE clause contains at least one MATCH_* condition.
The ORDER BY clause sorts by score, making it a Top-N query.

Example

SELECT *,
       score() AS relevance
FROM search_demo
WHERE content MATCH_ANY 'search test'
ORDER BY relevance DESC
LIMIT 10;

This query returns the top 10 records ranked by their BM25 score against the terms search and test.

Interpret the scores

Score range: BM25 scores are unbounded positive numbers. There is no fixed maximum. Use scores for relative comparison within a single query.

Multi-term queries: Each term in the query contributes its own score, and SelectDB sums them.

Length impact: Shorter records score slightly higher than longer records when they contain the same terms, because BM25 normalizes for record length. This reduces the advantage that longer records would otherwise have simply from containing more words.

No match: If a record contains none of the search terms, its score is 0.