Full-text search with text indexes

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/engines/table-engines/mergetree-family/textindexes.md)

Text indexes (also known as [inverted indexes](https://en.wikipedia.org/wiki/Inverted_index)) enable fast full-text search on text data.
A text index stores a mapping from tokens to the row numbers which contain each token.
Tokens are generated by a process called tokenization.
For example, ClickHouse's default tokenizer converts the English sentence "The cat likes mice." to tokens ["The", "cat", "likes", "mice"].

As an example, assume a table with a single column and three rows

```
1: The cat likes mice.
2: Mice are afraid of dogs.
3: I have two dogs and a cat.
```

The corresponding tokens are:

```
1: The, cat, likes, mice
2: Mice, are, afraid, of, dogs
3: I, have, two, dogs, and, a, cat
```

We usually like to search case-insensitively, therefore we lower-case the tokens:

```
1: the, cat, likes, mice
2: mice, are, afraid, of, dogs
3: i, have, two, dogs, and, a, cat
```

We will also remove fill words such as "I", "the" and "and" as they occur in almost every row:

```
1: cat, likes, mice
2: mice, afraid, dogs
3: have, two, dogs, cat
```

A text index then (conceptually) contains this information:

```
afraid : [2]
cat    : [1, 3]
dogs   : [2, 3]
have   : [3]
likes  : [1]
mice   : [1]
two    : [3]
```

Given a search token, this index structure allows to find all matching rows quickly.

## Creating a Text Index[​](#creating-a-text-index "Direct link to Creating a Text Index")

Text indexes are generally available (GA) in ClickHouse version 26.2 and newer.
In these versions, no special settings need to be configured to use the text index.
We strongly recommend using ClickHouse versions >= 26.2 for production use cases.

Note

Text indexes can be used with any ClickHouse version >= 26.2, regardless of the [compatibility](/docs/operations/settings/settings#compatibility) setting.

To create a text index use the following syntax:

```
CREATE TABLE table
(
    key UInt64,
    str String,
    INDEX text_idx(str) TYPE text(
                                -- Mandatory parameters:
                                tokenizer = splitByNonAlpha
                                            | splitByString[(S)]
                                            | ngrams[(N)]
                                            | asciiCJK
                                            | sparseGrams[(min_length[, max_length[, min_cutoff_length]])]
                                            | array
                                -- Optional parameters:
                                [, preprocessor = expression(str)]
                                -- Optional advanced parameters:
                                [, dictionary_block_size = D]
                                [, dictionary_block_frontcoding_compression = B]
                                [, posting_list_block_size = C]
                                [, posting_list_codec = 'none' | 'bitpacking' ]
                            )
)
ENGINE = MergeTree
ORDER BY key
```

Text indexes can be defined on columns of these types:

- [String](/docs/sql-reference/data-types/string) and [FixedString](/docs/sql-reference/data-types/fixedstring),
- [Array(String)](/docs/sql-reference/data-types/array) and [Array(FixedString)](/docs/sql-reference/data-types/array),
- [Map](/docs/sql-reference/data-types/map) (via [mapKeys](/docs/sql-reference/functions/tuple-map-functions#mapKeys) and [mapValues](/docs/sql-reference/functions/tuple-map-functions#mapValues) functions), and
- [JSON](/docs/sql-reference/data-types/newjson) (via [JSONAllPaths](/docs/sql-reference/functions/json-functions#JSONAllPaths) and [`JSONAllValues`](/docs/sql-reference/functions/json-functions#JSONAllValues) functions).

Columns of type [Nullable(T)](/docs/sql-reference/data-types/nullable) and [LowCardinality()](/docs/sql-reference/data-types/lowcardinality) are also supported, including `Array(Nullable(String or FixedString))`.

Alternatively, to add a text index to an existing table:

```
ALTER TABLE table
    ADD INDEX text_idx(str) TYPE text(
                                -- Mandatory parameters:
                                tokenizer = splitByNonAlpha
                                            | splitByString[(S)]
                                            | asciiCJK
                                            | ngrams[(N)]
                                            | sparseGrams[(min_length[, max_length[, min_cutoff_length]])]
                                            | array
                                -- Optional parameters:
                                [, preprocessor = expression(str)]
                                -- Optional advanced parameters:
                                [, dictionary_block_size = D]
                                [, dictionary_block_frontcoding_compression = B]
                                [, posting_list_block_size = C]
                                [, posting_list_codec = 'none' | 'bitpacking' ]
                            )
```

If you add an index to an existing table, we recommend materializing the index for existing table parts (otherwise search on parts without index will fall back to slow brute-force scans).

```
ALTER TABLE table MATERIALIZE INDEX text_idx SETTINGS mutations_sync = 2;
```

To remove a text index, please run

```
ALTER TABLE table DROP INDEX text_idx;
```

**Tokenizer argument (mandatory)**. The `tokenizer` argument specifies the tokenizer:

- `splitByNonAlpha` splits strings along non-alphanumeric ASCII characters (see function [splitByNonAlpha](/docs/sql-reference/functions/splitting-merging-functions#splitByNonAlpha)).
- `splitByString(S)` splits strings along certain user-defined separator strings `S` (see function [splitByString](/docs/sql-reference/functions/splitting-merging-functions#splitByString)).
  The separators can be specified using an optional parameter, for example, `tokenizer = splitByString([', ', '; ', '\n', '\\'])`.
  Note that each string can consist of multiple characters (`', '` in the example).
  The default separator list, if not specified explicitly (for example, `tokenizer = splitByString`), is a single whitespace `[' ']`.
- `asciiCJK` splits strings into tokens using Unicode word boundary rules (similar to [Unicode Text Segmentation (UAX #29)](https://unicode.org/reports/tr29/)). ASCII alphanumeric characters and underscores form tokens with connectors (ASCII `:` for letters, `.` and `'` for same-type characters). Non-ASCII Unicode characters, including [CJK](https://en.wikipedia.org/wiki/CJK_characters) characters, become single-character tokens.
- `ngrams(N)` splits strings into equally large `N`-grams (see function [ngrams](/docs/sql-reference/functions/splitting-merging-functions#ngrams)).
  The ngram length can be specified using an optional integer parameter between 1 and 8, for example, `tokenizer = ngrams(3)`.
  The default ngram size, if not specified explicitly (for example, `tokenizer = ngrams`), is 3.
- `sparseGrams(min_length, max_length, min_cutoff_length)` splits strings into variable-length n-grams of at least `min_length` and at most `max_length` (inclusive) characters (see function [sparseGrams](/docs/sql-reference/functions/string-functions#sparseGrams)).
  Unless specified explicitly, `min_length` and `max_length` default to 3 and 100.
  If parameter `min_cutoff_length` is provided, only n-grams with length greater or equal than `min_cutoff_length` are returned.
  Compared to `ngrams(N)`, the `sparseGrams` tokenizer produces variable-length N-grams, allowing for a more flexible representation of the original text.
  For example, `tokenizer = sparseGrams(3, 5, 4)` internally generates 3-, 4-, 5-grams from the input string but only the 4- and 5-grams are returned.
- `array` performs no tokenization, i.e. every row value is a token (see function [array](/docs/sql-reference/functions/array-functions#array)).

All available tokenizers are listed in [system.tokenizers](/docs/operations/system-tables/tokenizers).

Note