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 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:

CREATE CUSTOM INDEX ON ks.t (v) USING 'fulltext_index';

You can specify an analyzer to control how text is tokenized:

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 or clustering-key column. Partition-key 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.

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:

• 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.

For the full syntax reference, see Full-Text Search queries (BM25).

SELECT * FROM ks.t
    WHERE BM25(v, 'search term') > 0
    ORDER BY BM25(v, 'search term')
    LIMIT 10;

SELECT * FROM ks.t
    WHERE BM25(v, ?) > 0
    ORDER BY BM25(v, ?)
    LIMIT 10;

Unsupported ScyllaDB Features¶

Full-text indexes do not support all ScyllaDB features. The following features are not supported when using Full-Text Search:

• Tracing

• TTL (Time To Live)

• Paging

• Grouping (GROUP BY)

Query Constraints¶

FTS queries enforce the following rules: