Search indexes

A search index makes a field searchable through an analyzer that has already been defined. This page covers defining one, why each index takes a single field, and the parse error that follows from listing several.

Defining an index that uses an analyzer

Once a search analyzer is defined, it can be applied to the fields of a table to make them searchable by defining an index that uses the FULLTEXT ANALYZER clause.

DEFINE ANALYZER my_analyzer
  TOKENIZERS class
  FILTERS lowercase, ascii;

DEFINE INDEX body_index 
  ON TABLE article
  FIELDS body
  FULLTEXT ANALYZER my_analyzer;

DEFINE INDEX title_index
  ON TABLE article
  FIELDS title 
  FULLTEXT ANALYZER my_analyzer;

An index can only be defined on a single field.

DEFINE ANALYZER my_analyzer
  TOKENIZERS class
  FILTERS lowercase, ascii;

DEFINE INDEX body_index 
  ON TABLE article 
  FIELDS body, title 
  FULLTEXT ANALYZER my_analyzer;

'Parse error: Expected one column, found 2
 --> [5:55]
  |
5 | ...LDS body, title FULLTEXT ANALYZER my_analyzer;
  |              ^^^^^
'

Several indexes on one field

A field can carry more than one full-text index, each with its own analyzer. This suits a field that needs both exact word matching and stemmed matching, for example. The query planner picks one of the indexes on its own, so name the one to use with a WITH INDEX clause.

DEFINE ANALYZER simple TOKENIZERS class FILTERS lowercase;
DEFINE ANALYZER english TOKENIZERS class FILTERS lowercase, snowball(english);

DEFINE INDEX body_simple ON article FIELDS body FULLTEXT ANALYZER simple;
DEFINE INDEX body_english ON article FIELDS body FULLTEXT ANALYZER english;

CREATE article:one SET body = 'Running dogs';

SELECT id FROM article WITH INDEX body_simple WHERE body @@ 'run';
//- []

SELECT id FROM article WITH INDEX body_english WHERE body @@ 'run';
//- [{ id: article:one }]

The english analyzer stems Running to run, so only the index built with it matches the query term.

For querying with @@, BM25, and highlights, continue to Scoring and ranking.