# Full-Text Search in ScyllaDB ## What Is Full-Text Search Full-Text Search (FTS) lets you find rows that contain specific words or phrases inside text columns. Unlike exact-match queries or `LIKE` filters, FTS uses an inverted index to tokenize text and rank results by relevance using the [BM25](https://en.wikipedia.org/wiki/Okapi_BM25) scoring algorithm. Typical use cases include: * Searching for keywords in product descriptions, articles, or log messages * Ranking results by how well they match a search query * Filtering rows that contain at least one matching term ## Creating a Full-Text Index Before you can run FTS queries, you must create a `fulltext_index` on the target column: ```default CREATE CUSTOM INDEX ON ks.t (v) USING 'fulltext_index'; ``` You can specify an analyzer to control how text is tokenized. The default is `standard`; for a description of each supported analyzer, see [Full-text Index](https://docs.scylladb.com/manual/branch-2026.4/cql/secondary-indexes.md#create-fulltext-index-statement). ```default CREATE CUSTOM INDEX ON ks.t (v) USING 'fulltext_index' WITH OPTIONS = {'analyzer': 'english'}; ``` **Requirements:** * The indexed column must be of type `text`, `varchar`, or `ascii`. Other types are rejected. * The indexed column must be a regular column. Primary-key and static columns cannot be indexed. * The table must use tablets (not vnodes). * CDC must be enabled on the table with a TTL of at least 86400 seconds (24 hours) and either `delta = 'full'` or postimage enabled. CDC is enabled automatically when creating a fulltext index, so you do not need to configure it manually. Cell-level TTL, set with the standard `USING TTL` syntax on `INSERT` or `UPDATE`, makes the value unreadable at its expiration deadline but does not generate a CDC event, so the fulltext index is not updated and keeps a stale entry for that value. If the index must reflect expiration, use the [Per-row TTL](https://docs.scylladb.com/manual/branch-2026.4/cql/cql-extensions.md#cql-per-row-ttl) feature instead: it deletes expired rows explicitly and does generate a CDC event. ## Querying with BM25 FTS queries use the `BM25()` function to score rows against a search term. `BM25()` takes two arguments: a column name and a query string. A full-text search query must use `BM25()` in **both** of these clauses, on the **same** column and with the **same** search term: * A `WHERE` clause of the exact form `BM25(column, 'term') > 0` to **filter** the rows that match the search term. * An `ORDER BY BM25(column, 'term')` clause to **rank** the matching rows by their BM25 relevance score (highest first). Neither clause works on its own - a query with only `WHERE BM25()` or only `ORDER BY BM25()` is rejected, and both must reference the **same column** and the **same search term**. Every FTS query also requires a `LIMIT`. The exception is a [hybrid query](https://docs.scylladb.com/manual/branch-2026.4/cql/dml/select.md#hybrid-queries), which ranks the rows by a full-text search together with other searches, such as a vector search, and takes no `WHERE` clause. For the full syntax reference, see [Full-Text Search queries (BM25)](https://docs.scylladb.com/manual/branch-2026.4/cql/dml/select.md#fulltext-queries). ### Basic query Filter the rows that contain the search term and rank them by relevance: ```default SELECT * FROM ks.t WHERE BM25(v, 'search term') > 0 ORDER BY BM25(v, 'search term') LIMIT 10; ``` In the `WHERE` clause, `>` is the only supported operator and the right-hand side must be the literal `0`. Operators such as `>=`, `=`, `<`, `<=`, and `!=` are rejected, as is any non-zero threshold. `WHERE BM25(v, 'search term') > 0` compares the **score**, and is the same as `WHERE BM25_SCORE(v, 'search term') > 0`. Those are the only two calls the clause accepts. `BM25()` may also be selected. It returns a `tuple`: the **score** the index gave the row, and the row’s **rank**, its position in the index’s result counted from 1: ```default SELECT id, BM25(v, 'search term') AS score_and_rank FROM ks.t WHERE BM25(v, 'search term') > 0 ORDER BY BM25(v, 'search term') LIMIT 10; ``` `BM25_SCORE()` and `BM25_RANK()` return the two values on their own, which is usually what a query wants: ```default SELECT id, BM25_SCORE(v, 'search term') AS score, BM25_RANK(v, 'search term') AS rank FROM ks.t WHERE BM25(v, 'search term') > 0 ORDER BY BM25(v, 'search term') LIMIT 10; ``` All three describe the search the rows are ranked by, so each needs a BM25 search on the same column in the `ORDER BY` clause, and has to use the same search term as that search. A query using several of them still makes a single request. The rank is the position in the index’s result, not in the result set. If the index returns a row that is no longer in the base table, that row’s rank is missing from the result and the ranks after it are not renumbered. ### Highlighting `BM25_HIGHLIGHT()` returns an excerpt of the searched text with the matched terms marked, for showing the reader why a row was returned: ```default SELECT id, BM25_HIGHLIGHT(v, 'search term') AS excerpt FROM ks.t WHERE BM25(v, 'search term') > 0 ORDER BY BM25(v, 'search term') LIMIT 10; ``` It is accepted only in the `SELECT` clause, under the same rules as `BM25()` there: the `ORDER BY` clause must have a BM25 search on the same column, and the call must use the same search term as that search. The result is a single fragment of type `text`, holding HTML: the matched terms are wrapped in `` and ``, and the text around them is HTML-escaped, so the fragment can be inserted into an HTML document as returned. The value is `null` for a row in which no fragment could be found, as happens when the query consists only of stop words. Such a row is still returned. A query that asks for an excerpt costs one additional round trip to the full-text index, made after the matching rows have been read, carrying the text of the highlighted column of those rows. In a query that ranks the rows by several searches, only the rows the full-text search returned are sent, and the others get a `null` excerpt. Queries that do not ask for an excerpt are unaffected. ### Filtering support Additional `WHERE` restrictions (such as partition key equality) are not currently supported alongside `BM25()` and will be rejected. Support for combined filtering is planned for a future release. ### Using bind markers The query term can be supplied with a bind marker in prepared statements: ```default SELECT * FROM ks.t WHERE BM25(v, ?) > 0 ORDER BY BM25(v, ?) LIMIT 10; ``` All the search-term bind markers are checked at execution time and must be given the same value; binding different values for the `WHERE` and `ORDER BY` clauses or for a selected `BM25()` causes the query to be rejected. ### Disambiguating from a user-defined function `BM25` is not a reserved word. If a keyspace defines a user-defined function named `bm25`, an unqualified `BM25()` call becomes ambiguous; qualify the built-in operator as `system.bm25(...)` to select it explicitly. ## Query Constraints FTS queries enforce the following rules: | Constraint | Details | |---------------------------------------------------------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | Both clauses required | A query must include both a `WHERE BM25() > 0` filter and an
`ORDER BY BM25()` ranking. Both must reference the same column and
the same search term. Neither clause is accepted on its own. The
exception is a [hybrid query](https://docs.scylladb.com/manual/branch-2026.4/cql/dml/select.md#hybrid-queries), which takes no
`WHERE` clause. | | `>` and literal `0` only | In `WHERE`, the only accepted form is `BM25(column, 'term') > 0`.
Other operators (`>=`, `=`, `<`, `<=`, `!=`) and non-zero
thresholds are rejected. | | Filtering support | Only `BM25(column, 'term') > 0` is accepted in `WHERE`. Any additional
restriction (e.g., a partition key equality) is rejected. Combined filtering
is planned for a future release. | | `LIMIT` is required | Every FTS query must include a `LIMIT` clause of at most 1000.
Queries without `LIMIT`, or with a `LIMIT` greater than 1000,
are rejected. | | `PER PARTITION LIMIT` not supported | `PER PARTITION LIMIT` cannot be used with FTS queries. | | Aggregation not supported | FTS queries cannot include aggregate functions (e.g., `COUNT(*)`,
`SUM()`). | | Fulltext index required | The queried column must have a `fulltext_index`. A regular secondary
index does not satisfy this requirement. | | Selecting a score or a rank needs a BM25 search in `ORDER BY` | `BM25()` returns a `tuple`, the row’s relevance score and
its rank; `BM25_SCORE()` and `BM25_RANK()` return one element each.
Any of them may be used as a selector, but only in a query whose
`ORDER BY` clause has a BM25 search on the same column, and every
occurrence must use the same search term as that search. | | `BM25_HIGHLIGHT()` is a selector only | `BM25_HIGHLIGHT()` is accepted in the `SELECT` clause under the same
rules as `BM25()`, and rejected in every other clause. See
[Highlighting](#fulltext-highlighting). | | Single ordering only | `ORDER BY BM25()` cannot be combined with other `ORDER BY` columns.
To rank the rows by several searches, fuse them in a
[hybrid query](https://docs.scylladb.com/manual/branch-2026.4/cql/dml/select.md#hybrid-queries). | | Paging not supported | FTS queries do not support paging; all matching rows up to `LIMIT`
are returned in a single page. | | Grouping not supported | FTS queries cannot include a `GROUP BY` clause. | | `DISTINCT` not supported | `SELECT DISTINCT` is not supported in FTS queries. It is currently
accepted, but does not remove duplicate partition keys: one row is
returned per matching row. |