Working with Full Text Search¶

Prerequisites¶

• A cluster with Vector and Text Search enabled, running ScyllaDB 2026.3.0 or later. See Creating a New Cluster with Vector and Text Search Enabled to create one, or Enabling Vector and Text Search on an Existing Cluster to add indexing nodes to a cluster you already have.

• cqlsh, the web-based CQL console in the ScyllaDB Cloud UI, or any CQL driver.

Workflow¶

1. Create a table with a text, varchar, or ascii column holding the text you want to search.

2. Create a full-text index on that column.

3. Wait for the index to finish building.

4. Run BM25() queries against the indexed column.

Creating a Full-Text Index¶

Create a full-text index with CREATE CUSTOM INDEX and the fulltext_index class:

CREATE KEYSPACE blog
  WITH replication = {'class': 'NetworkTopologyStrategy', 'replication_factor': 3};

CREATE TABLE blog.articles (
  id int PRIMARY KEY,
  author text,
  body text
);

CREATE CUSTOM INDEX articles_body_fts ON blog.articles(body)
  USING 'fulltext_index';

IF NOT EXISTS is supported, and the index name may be omitted — ScyllaDB then derives one from the table and column names, such as articles_body_idx.

Use DESCRIBE INDEX to inspect an existing index:

DESCRIBE INDEX blog.articles_body_fts;

CREATE CUSTOM INDEX articles_body_fts ON blog.articles(body)
  USING 'fulltext_index'
  WITH OPTIONS = {'analyzer': 'english'};

Invalid value in option 'analyzer' for fulltext index: 'klingon'. Supported
are case-insensitive: standard, english, german, french, spanish, italian,
portuguese, russian, simple, whitespace

Invalid value in option 'positions' for fulltext index: 'sometimes'.
Supported are case-insensitive: false, true

Unsupported option nonsense for fulltext index

# Immediately after CREATE, before the indexing node has picked up the index
Vector Store error: HTTP status 404 Not Found, message: missing index: blog.articles_body_fts

# While the base table is being scanned
Vector Store error: HTTP status 503 Service Unavailable, message: Index
blog.articles_body_fts is not available yet as it is still being constructed,
progress: 39.804%

Running Full Text Search Queries¶

A full text search query has exactly one valid shape:

SELECT <columns> FROM <table>
  WHERE BM25(<column>, '<query>') > 0
  ORDER BY BM25(<column>, '<query>')
  LIMIT <n>;

For example:

SELECT id, body FROM blog.articles
  WHERE BM25(body, 'photosynthesis') > 0
  ORDER BY BM25(body, 'photosynthesis')
  LIMIT 10;

Every part of that shape is mandatory, and ScyllaDB rejects any deviation. The rules are:

ORDER BY BM25() does not accept ASC or DESC; results always come back with the highest relevance first.

Paging is not supported for Full-Text Search queries. The entire result set
has been returned.

Text Query Language¶

The string you pass to BM25() is written in its own text query language — it is not CQL, and it is not treated as a literal phrase. The following constructs are supported.

-- These two queries return the same rows.
SELECT id FROM blog.articles
  WHERE BM25(body, 'photosynthesis') > 0
  ORDER BY BM25(body, 'photosynthesis') LIMIT 10;

SELECT id FROM blog.articles
  WHERE BM25(body, 'PHOTOSYNTHESIS') > 0
  ORDER BY BM25(body, 'PHOTOSYNTHESIS') LIMIT 10;

-- Rows that contain both terms.
SELECT id FROM blog.articles
  WHERE BM25(body, 'database AND distributed') > 0
  ORDER BY BM25(body, 'database AND distributed') LIMIT 10;

-- Rows that contain either term.
SELECT id FROM blog.articles
  WHERE BM25(body, 'jupiter OR saturn') > 0
  ORDER BY BM25(body, 'jupiter OR saturn') LIMIT 10;

-- Rows that contain the first term but not the second.
SELECT id FROM blog.articles
  WHERE BM25(body, 'python NOT snake') > 0
  ORDER BY BM25(body, 'python NOT snake') LIMIT 10;

-- Grouping.
SELECT id FROM blog.articles
  WHERE BM25(body, '(jupiter OR saturn) AND planet NOT rings') > 0
  ORDER BY BM25(body, '(jupiter OR saturn) AND planet NOT rings') LIMIT 10;

SELECT id FROM blog.articles
  WHERE BM25(body, '"theory of relativity"') > 0
  ORDER BY BM25(body, '"theory of relativity"') LIMIT 10;

CREATE CUSTOM INDEX articles_body_fts ON blog.articles(body)
  USING 'fulltext_index'
  WITH OPTIONS = {'positions': 'false'};

Vector Store error: HTTP status 400 Bad Request, message: index.bm25 request
error: fts: failed to parse query: The field 'body' does not have positions
indexed

Relevance Ranking¶

Matches are ordered by their BM25 score, highest first. BM25 scores a row on how often the query terms occur in it, offset by how common those terms are across the whole index and by the length of the indexed text: a term that appears in nearly every row contributes little, and a short row that mentions the term as often as a long one scores higher.

Scores are relative to the current contents of the index and are not exposed — BM25() cannot appear in the SELECT list, so you get the ordering rather than the numbers.

Write-to-Query Latency¶

Inserts, updates, and deletes reach the full-text index asynchronously through CDC, so a write becomes searchable a short time after it is acknowledged — expect a few seconds. Deleting a row removes it from the index, and updating an indexed column replaces the old text, so the previous terms stop matching.

If a query must reflect a write immediately, read the row by primary key instead.

Combining Full Text and Vector Search¶

A table can carry both a full-text index and a vector index, which is what makes hybrid search possible:

CREATE TABLE blog.docs (
  id int PRIMARY KEY,
  body text,
  embedding vector<float, 3>
);

CREATE CUSTOM INDEX docs_body_fts ON blog.docs(body) USING 'fulltext_index';
CREATE INDEX docs_embedding_ann ON blog.docs(embedding) USING 'vector_index';

The two indexes are queried separately — BM25() and ANN OF cannot appear in the same statement:

-- Term matching.
SELECT id, body FROM blog.docs
  WHERE BM25(body, 'apple') > 0
  ORDER BY BM25(body, 'apple') LIMIT 10;

-- Semantic similarity.
SELECT id, body FROM blog.docs
  ORDER BY embedding ANN OF [1.0, 0.1, 0.0] LIMIT 10;

To build hybrid search, run both queries and merge the two result sets in your application, for example with Reciprocal Rank Fusion, which combines the rank of each row in each list without needing comparable scores.

Using Full Text Search from a Driver¶

Full text search needs no driver-side support beyond ordinary CQL. Prepared statements accept bind markers for the search term — in both the WHERE and ORDER BY positions — and for the LIMIT.

The example below assumes an open session. Connecting to a ScyllaDB Cloud cluster requires authentication, TLS, and a datacenter-aware load balancing policy — see Driver Examples for a complete connection example.

search = session.prepare(
    "SELECT id, body FROM articles "
    "WHERE BM25(body, ?) > 0 "
    "ORDER BY BM25(body, ?) "
    "LIMIT ?"
)


def full_text_search(query, limit=10):
    # The same term has to be bound to both markers; the server rejects the
    # statement if the two values differ.
    return session.execute(search, (query, query, limit))


for row in full_text_search("database AND distributed"):
    print(row.id, row.body)

Because the bound value is parsed as a query expression, sanitize end-user input before passing it in — see Unsupported Query Syntax. To search for the words a user typed without interpreting them as query syntax, keep only word characters and whitespace, then drop anything the parser would read as an operator:

import re

# Keep letters, digits and underscore; replace every other character with a
# space. A whitelist is safer here than a list of reserved characters: it
# cannot fall behind the parser, and it cannot leave an unbalanced
# parenthesis or a stray operator behind.
NON_WORD = re.compile(r"[^\w\s]", re.UNICODE)
OPERATORS = {"AND", "OR", "NOT"}


def as_terms(user_input):
    """Reduce free-form input to a list of plain, safe search terms."""
    cleaned = NON_WORD.sub(" ", user_input)
    return [term for term in cleaned.split() if term not in OPERATORS]


terms = as_terms(user_input)
if terms:
    # Whitespace joins the terms with an implicit OR, which keeps the search
    # forgiving; join with " AND " instead when every term must be present.
    for row in full_text_search(" ".join(terms)):
        print(row.id, row.body)

Order matters in that function: characters are replaced before the operator check. Stripping first turns AND) into AND, which the operator filter then removes — whereas filtering first would leave AND) in place, and the query would fail on the unbalanced parenthesis. Because \w is Unicode-aware, accented and non-Latin words survive intact.

Stripping punctuation can split a word — don't becomes the two terms don and t — but under the default standard analyzer that costs you nothing on its own, because the indexed text is tokenized the same way: a row containing don’t is indexed under don and t too, so it still matches. Note that this does not hold for the whitespace analyzer, which keeps punctuation, so there the indexed term is don't and neither don nor don t matches it.

The reason to prefer the implicit OR for free-form input is that AND requires every term to be present, so a single word the user typed that appears in no document reduces the whole result set to nothing:

-- 'unicorn' appears in no document, so the AND query matches nothing
-- while the implicit OR still returns the rows that mention a database.
SELECT id FROM blog.articles
  WHERE BM25(body, 'database AND unicorn') > 0
  ORDER BY BM25(body, 'database AND unicorn') LIMIT 10;

SELECT id FROM blog.articles
  WHERE BM25(body, 'database unicorn') > 0
  ORDER BY BM25(body, 'database unicorn') LIMIT 10;

Note also that sanitizing changes only the surface form of the words, not their meaning: a search for don’t will not reach text that says do not, because those are different terms. Matching by meaning rather than by word is what vector search is for, and Combining Full Text and Vector Search combines the two.

See ScyllaDB Drivers for the list of supported drivers and connection details.

Dropping a Full-Text Index¶

DROP INDEX blog.articles_body_fts;

Dropping the index releases the memory it used on the indexing nodes and leaves the base table untouched. Full text search queries against the column then fail with No fulltext index found for full-text search query.

Limitations¶

• Full text search requires ScyllaDB 2026.3.0 or later.

• A full-text index can only target a text, varchar, or ascii column, and not a partition key column.

• LIMIT is mandatory and must not exceed 1000. Results are not paged.

• A full text search query cannot carry any other WHERE restriction, and cannot be combined with an ANN OF clause.

• Only one BM25() restriction is allowed per query.

• BM25 scores cannot be projected in the SELECT list.

• Fuzzy matching (term~1) and prefix matching (term*) are not available.

• Stemming is available only through the language analyzers, and the analyzer applies to the whole index — it cannot be chosen per query.

• Index options cannot be changed after creation — there is no ALTER INDEX statement. Drop the index and create it again instead.

• Cell-level TTL (USING TTL, default_time_to_live) is not supported: expired rows leave stale entries in the index. Use per-row TTL instead.

Next Steps¶

• Working with Vector and Text Search — the vector search half of the feature.

• Full Text Search CQL Reference — the statements and index options on one page.

• Troubleshooting — common full text search problems.

• FAQ