Fulltext index

Fulltext indexes in row-oriented tables are created using the same syntax as secondary indexes, by specifying fulltext_plain or fulltext_relevance as the index type. Subset of syntax available for fulltext indexes:

CREATE TABLE `<table_name>` (
    ...
    INDEX `<index_name>`
        GLOBAL
        [SYNC]
        USING fulltext_plain | fulltext_relevance
        ON ( [<prefix_columns>,] <text_column> )
        [COVER ( <cover_columns> )]
        WITH ( <parameter_name> = <parameter_value>[, ...])
    [,   ...]
)

Where:

• <index_name> - unique index name for data access
• SYNC - indicates synchronous data writing to the index. This is the only currently available option, and it is used by default.
• <prefix_columns> - optional filter columns for a filtered fulltext index; queries require equality on every prefix column
• <text_column> - a single String or Utf8 column, specified last in ON
• <cover_columns> - list of additional table columns stored in the index to enable retrieval without accessing the main table
• <parameter_name> and <parameter_value> - list of key-value parameters:

• common parameters for all fulltext indexes:
  • analyzer - predefined analyzer: standard (standard tokenizer, lowercase and English stopword removal), snowball (standard tokenizer, lowercase, stopword removal, and Snowball lemmatization; requires language), or keyword (the whole text is one token, preserving case)
  • tokenizer - tokenizer type (standard, whitespace, alphanumeric, or keyword)
  • use_filter_lowercase - lowercase filter (true or false)
  • use_filter_stopwords - remove common words such as the and and (true or false). Supports English and Russian; defaults to English when language is omitted
  • use_filter_length - token length filter (true or false); when true, tokens shorter than filter_length_min or longer than filter_length_max are not indexed
  • filter_length_min - minimum token length (positive integer); only applied when use_filter_length=true
  • filter_length_max - maximum token length (positive integer); only applied when use_filter_length=true
  • use_filter_snowball - Snowball lemmatization filter (true or false)
  • language - one language or a comma-separated list for the Snowball lemmatizer. Spaces around commas are allowed, for example "english, russian". The snowball analyzer also removes stopwords, so with that preset only english and russian can be set
  • use_filter_ngram - n-gram filter (true or false)
  • use_filter_edge_ngram - edge n-gram filter (true or false)
  • filter_ngram_min_length - minimum n-gram length (positive integer)
  • filter_ngram_max_length - maximum n-gram length (positive integer)

Examples

Basic example

CREATE TABLE articles (
    id Uint64,
    title String,
    body String,
    INDEX ft_idx GLOBAL USING fulltext_relevance
    ON (body) COVER (title)
    WITH (
        tokenizer=standard,
        use_filter_lowercase=true
    ),
    PRIMARY KEY (id)
)

Multilingual lemmatization

The Snowball preset lemmatizes tokens and matches English and Russian word forms in the same text column. language accepts several supported languages, separated by commas. Spaces after commas are allowed:

CREATE TABLE documents (
    id Uint64,
    body Utf8,
    PRIMARY KEY (id),
    INDEX ft_idx GLOBAL USING fulltext_relevance ON (body)
    WITH (analyzer="snowball", language="english, russian")
);

Example with n-grams

N-grams allow finding substrings within words. When using the n-gram filter, each word is split into all possible substrings of specified length. For example, the word "search" with parameters filter_ngram_min_length=3 and filter_ngram_max_length=5 will be split into: "sea", "ear", "arc", "rch" (3-grams), "sear", "earc", "arch" (4-grams), "searc", "earch" (5-grams). This enables finding documents by partial matches anywhere within a word.

CREATE TABLE products (
    id Uint64,
    name String,
    description String,
    INDEX ft_ngram_idx GLOBAL USING fulltext_plain
    ON (name)
    WITH (
        tokenizer=standard,
        use_filter_lowercase=true,
        use_filter_ngram=true,
        filter_ngram_min_length=3,
        filter_ngram_max_length=5
    ),
    PRIMARY KEY (id)
)

Example with edge n-grams

Edge n-grams create substrings only from the beginning of a word. For example, the word "search" with parameters filter_ngram_min_length=2 and filter_ngram_max_length=10 will be split into: "se", "sea", "sear", "searc", "search". This is particularly useful for implementing autocomplete functionality, when you need to find words that start with a user-entered prefix.

CREATE TABLE users (
    id Uint64,
    username String,
    email String,
    INDEX ft_edge_ngram_idx GLOBAL USING fulltext_plain
    ON (username)
    WITH (
        tokenizer=standard,
        use_filter_lowercase=true,
        use_filter_edge_ngram=true,
        filter_ngram_min_length=2,
        filter_ngram_max_length=10
    ),
    PRIMARY KEY (id)
)