SEARCH Index (Full-Text Search)

Level 4 — Schema Definition & Constraints The specialized index type in SurrealDB designed for full-text search, supporting tokenization, language stemming, stopword filtering, and industry-standard BM25 relevance ranking natively.

1. Prerequisites

• DEFINE INDEX (Deep Dive) — The parent index context.

2. Term Category

Performance / Operations (full-text search index definition): - Database Structure / Paradigm

3. Explanation

(1) Design Motivation — "Why did we design this?"

Standard B-Tree indexes are optimized for exact matches (=) or range lookups (<, >).

However, they fail at text search:

• If a user searches a blogging site for "database rust", standard indexes cannot look inside a text block and find posts containing both words in different orders.
• Using substring checks (like WHERE content ~ "database") triggers slow table scans and cannot sort results by relevance (relevance ranking).

In PostgreSQL, developers use tsvector or spin up a separate Elasticsearch cluster, adding to infrastructure costs and sync pipelines.

We designed the SEARCH index in SurrealDB to build full-text search directly into the database.

It tokenizes text, removes common filler words (stopwords), reduces words to their roots (stemming), and ranks matches using the BM25 algorithm (the same standard used by Elasticsearch), eliminating the need for a separate search engine.

(2) Full-Text Search Core Concepts

• Analyzers: Configurations that define how text is processed. An analyzer tokenizes text, converts it to lowercase, and filters out punctuation.
• Stemming: Converts words to their root form (e.g., "running", "runs", and "ran" are all mapped to the root "run").
• Stopwords: Ignores common connector words (like "the", "is", and "and") to keep the index clean.
• BM25 Relevance: An algorithm that scores documents based on how often search terms appear in them, ensuring the best matches are returned first.
• The Search Operator (@): Used in SurrealQL to execute search queries: WHERE content @1@ "search query".

(3) Reality Metaphor (The News Archivist)

Imagine searching a huge newspaper warehouse for articles about dogs:

• Standard B-Tree Index: A list of article titles. You can find "The Dog Breed Guide", but you miss articles containing the word "dogs" inside the paragraph text.
• SEARCH Index: A Professional Archivist.
  • The archivist reads every article, strips out words like "the" and "a" (stopwords), converts "running" to "run" (stemming), and indexes key terms.
  • When you ask for "running dogs", they find the roots "run" and "dog", calculate which articles mention them most frequently (BM25), and present the most relevant clippings first.

(4) Code Examples

Building and Querying Full-Text Indexes

Let's optimize a blog article search schema:

DEFINE TABLE article SCHEMAFULL;
DEFINE FIELD title ON article TYPE string;
DEFINE FIELD content ON article TYPE string;

-- 1. Define an analyzer for English text search
DEFINE ANALYZER english_search TOKENIZERS class FILTERS lowercase, snowball(english);

-- 2. Define the search index on the title and content fields
DEFINE INDEX article_search ON article COLUMNS title, content
  SEARCH ANALYZER english_search BM25;

-- 3. Insert mock records
CREATE article SET title = "SurrealDB Relational Design", content = "Learn how to traverse document graphs.";
CREATE article SET title = "Rust Programming", content = "Building high-performance databases using Rust.";

-- 4. Search the index using the FTS operator '@'
-- Matches posts containing 'database' or 'rust', sorted by relevance!
SELECT title, search::score(1) AS relevance_score
FROM article
WHERE content @1@ "database rust"
ORDER BY relevance_score DESC;

4. Common Mistakes & Pitfalls

Mistake 1: Attempting to run search match queries using the '@' operator on fields that have not been configured with a 'SEARCH' index

The mistake: Running the query SELECT * FROM post WHERE content @1@ "rust"; when content only has a standard B-Tree index defined.

Why it's wrong: The @ full-text search operator relies on the specialized index tables generated by the SEARCH compiler.

If no search index is active on the field, the parser throws an index missing exception and rejects the query.

Fix: Always define a SEARCH index with an analyzer before running full-text match queries:

-- CORRECT SEQUENCE
DEFINE INDEX post_search ON post COLUMNS content SEARCH ANALYZER english_search;
SELECT * FROM post WHERE content @1@ "rust";

Mistake 2: Querying Full-Text Search Indexes Without SEARCH or @@ Operators

The mistake: Writing SELECT * FROM article WHERE content CONTAINS 'rust'; expecting full-text search index scoring.

Why it's wrong: CONTAINS performs simple collection matching. Full-text search index matching requires the SEARCH operator or @@ operator with search::score().

Incorrect:

-- Expecting Full-Text Search index utilization
SELECT * FROM article WHERE content CONTAINS "rust";

Fix:

SELECT *, search::score(0) AS score FROM article WHERE content SEARCH 'rust' ORDER BY score DESC;

Mistake 3: Omitting Analyzer Configurations on Search Indexes

The mistake: Defining DEFINE INDEX search_idx ON TABLE article FIELDS content SEARCH; without specifying tokenizers or language analyzers.

Why it's wrong: Without specifying analyzers (e.g. BM25, HIGHLIGHTS, tokenizers blank, class, snowball), search indexes use basic defaults that may not support stemming or highlighting.

Incorrect:

DEFINE INDEX content_idx ON TABLE article FIELDS content SEARCH; // Basic defaults

Fix:

DEFINE INDEX content_idx ON TABLE article FIELDS content SEARCH BM25 HIGHLIGHTS ANALYZER blank, snowball(english);

5. Practice Exercises

Exercise 1: Defining Full-Text Search Indexes

Scenario: Create a full-text search index article_search on table article covering fields title and content.

Requirements:

1. Write DEFINE INDEX article_search ON TABLE article COLUMNS title, content SEARCH ANALYZER blank BM25.

DEFINE TABLE article SCHEMAFULL;
DEFINE FIELD title ON TABLE article TYPE string;
DEFINE FIELD content ON TABLE article TYPE string;

-- Define full-text search index with BM25 scoring
DEFINE INDEX article_search ON TABLE article COLUMNS title, content SEARCH ANALYZER blank BM25;

Exercise 2: Executing Full-Text Search Queries

Scenario: Search for articles containing term "SurrealDB" using the @@ search operator.

Requirements:

1. Write SELECT * FROM article WHERE title @@ "SurrealDB".

CREATE article:a1 SET title = "Learning SurrealDB Basics", content = "Full-text search engine...";

-- Execute full-text search query
SELECT * FROM article WHERE title @@ "SurrealDB";

Exercise 3: Highlights and Search Score Retrieval

Scenario: Retrieve search relevance scores (search::score()) for matching articles.

Requirements:

1. Project search::score(1) in SELECT.

SELECT title, search::score(0) AS score 
FROM article 
WHERE title @@ "SurrealDB" 
ORDER BY score DESC;

6. Related Terms

• DEFINE INDEX (Deep Dive) — The parent index context.
• Full-Text Search (tsvector, tsquery) — Query operators.
• Search Index & DEFINE ANALYZER — Search analyzers and tokenizers.
• search::* Functions & @@ Operator — search::score() search functions.

7. Key Takeaways

• SEARCH indexes enable full-text search directly inside SurrealDB.
• Replaces the need to spin up separate search servers like Elasticsearch.
• Uses Analyzers to tokenize text, filter stopwords, and stem words to their roots.
• Stemming matches different word forms (e.g. "running" matches "run").
• Standard BM25 algorithms rank returned documents by search term relevance.
• Search queries are executed using the @<id>@ match operator in SurrealQL.
• Ensure fields have a SEARCH index configured before running match queries.