Full-text search with text indexes

*Index granularity.*
Text indexes are implemented within ClickHouse as a type of [skip indexes](/docs/engines/table-engines/mergetree-family/mergetree#skip-index-types).
However, unlike other skip indexes, text indexes use an infinite granularity (100 million).
This can be seen in the table definition of a text index.

Example:

```
CREATE TABLE table(
    k UInt64,
    s String,
    INDEX idx(s) TYPE text(tokenizer = ngrams(2)))
ENGINE = MergeTree()
ORDER BY k;

SHOW CREATE TABLE table;
```

Result:

```
┌─statement──────────────────────────────────────────────────────────────┐
│ CREATE TABLE default.table                                            ↴│
│↳(                                                                     ↴│
│↳    `k` UInt64,                                                       ↴│
│↳    `s` String,                                                       ↴│
│↳    INDEX idx s TYPE text(tokenizer = ngrams(2)) GRANULARITY 100000000↴│ <-- here
│↳)                                                                     ↴│
│↳ENGINE = MergeTree                                                    ↴│
│↳ORDER BY k                                                            ↴│
│↳SETTINGS index_granularity = 8192                                      │
└────────────────────────────────────────────────────────────────────────┘
```

The huge index granularity ensures that the text index is created for the entire part.
An explicitly specified index granularity is ignored.

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

Using a text index in SELECT queries is straightforward as common string search functions will leverage the index automatically.
If no index exists on a column or table part, the string search functions will fall back to slow brute-force scans.

Note

We recommend using functions `hasAnyTokens` and `hasAllTokens` to search the text index, please see [below](#functions-example-hasanytokens-hasalltokens).
These functions work with all available tokenizers and all possible preprocessor expressions.
As the other supported functions historically preceded the text index, they had to retain their legacy behavior in many cases (e.g. no preprocessor support).

### Supported functions[​](#functions-support "Direct link to Supported functions")

The text index can be used if text functions are used in the `WHERE` clause or `PREWHERE` clauses:

```
SELECT [...]
FROM [...]
WHERE string_search_function(column_with_text_index)
```

#### `=`[​](#functions-example-equals "Direct link to functions-example-equals")

`=` ([equals](/docs/sql-reference/functions/comparison-functions#equals)) matches the entire given search term.

Example:

```
SELECT * from table WHERE str = 'Hello';
```

#### `IN`[​](#functions-example-in "Direct link to functions-example-in")

`IN` ([in](/docs/sql-reference/functions/in-functions)) is similar to `equals` but matches all search terms.

Example:

```
SELECT * from table WHERE str IN ('Hello', 'World');
```

Note

`NOT IN` (`notIn`) is not supported by the text index.

#### `LIKE` and `match`[​](#functions-example-like-match "Direct link to functions-example-like-match")

Note

These functions currently use the text index for filtering only if the index tokenizer is either `splitByNonAlpha`, `ngrams` or `sparseGrams`.

Note

`NOT LIKE` (`notLike`) is not supported by the text index.

In order to use `LIKE` ([like](/docs/sql-reference/functions/string-search-functions#like)) and the [match](/docs/sql-reference/functions/string-search-functions#match) function with text indexes, ClickHouse must be able to extract complete tokens from the search term.
For the index with `ngrams` tokenizer, this is the case if the length of the searched strings between wildcards is equal or longer than the ngram length.

Example for the text index with `splitByNonAlpha` tokenizer:

```
SELECT count() FROM table WHERE comment LIKE 'support%';
```

`support` in the example could match `support`, `supports`, `supporting` etc.
This kind of query is a substring query and it cannot be sped up by a text index.

To leverage a text index for LIKE queries, the LIKE pattern must be rewritten in the following way:

```
SELECT count() FROM table WHERE comment LIKE ' support %'; -- or `% support %`
```

The spaces left and right of `support` make sure that the term can be extracted as a token.

Fortunately, there is a special case where ClickHouse can leverage the inverted index to speed up LIKE queries significantly.

See the [LIKE/ILIKE performance tuning section](#like-ilike-queries-perf) for details.

#### `startsWith` and `endsWith`[​](#functions-example-startswith-endswith "Direct link to functions-example-startswith-endswith")

Similar to `LIKE`, functions [startsWith](/docs/sql-reference/functions/string-functions#startsWith) and [endsWith](/docs/sql-reference/functions/string-functions#endsWith) can only use a text index, if complete tokens can be extracted from the search term.
For the index with `ngrams` tokenizer, this is the case if the length of the searched strings between wildcards is equal or longer than the ngram length.

Example for the text index with `splitByNonAlpha` tokenizer:

```
SELECT count() FROM table WHERE startsWith(comment, 'clickhouse support');
```

In the example, only `clickhouse` is considered a token.
`support` is no token because it can match `support`, `supports`, `supporting` etc.

To find all rows that start with `clickhouse supports`, please end the search pattern with a trailing space:

```
startsWith(comment, 'clickhouse supports ')`
```

Similarly, `endsWith` should be used with a leading space:

```
SELECT count() FROM table WHERE endsWith(comment, ' olap engine');
```

#### `hasToken` and `hasTokenOrNull`[​](#functions-example-hastoken-hastokenornull "Direct link to functions-example-hastoken-hastokenornull")

Note

Function `hasToken` looks straightforward to use but it has certain pitfalls with non-default tokenizers and preprocessor expressions.
We recommend using functions `hasAnyTokens` and `hasAllTokens` instead.

Functions [hasToken](/docs/sql-reference/functions/string-search-functions#hasToken) and [hasTokenOrNull](/docs/sql-reference/functions/string-search-functions#hasTokenOrNull) match against a single given token.

Unlike the previously mentioned functions, they do not tokenize the search term (they assume the input is a single token).

Example:

```
SELECT count() FROM table WHERE hasToken(comment, 'clickhouse');
```

#### `hasAnyTokens` and `hasAllTokens`[​](#functions-example-hasanytokens-hasalltokens "Direct link to functions-example-hasanytokens-hasalltokens")

Functions [hasAnyTokens](/docs/sql-reference/functions/string-search-functions#hasAnyTokens) and [hasAllTokens](/docs/sql-reference/functions/string-search-functions#hasAllTokens) match against one or all of the given tokens.

These two functions accept the search tokens as either a string which will be tokenized using the same tokenizer used for the index column, or as an array of already processed tokens to which no tokenization will be applied prior to searching.
See the function documentation for more info.

Example:

```
-- Search tokens passed as string argument
SELECT count() FROM table WHERE hasAnyTokens(comment, 'clickhouse olap');
SELECT count() FROM table WHERE hasAllTokens(comment, 'clickhouse olap');

-- Search tokens passed as Array(String)
SELECT count() FROM table WHERE hasAnyTokens(comment, ['clickhouse', 'olap']);
SELECT count() FROM table WHERE hasAllTokens(comment, ['clickhouse', 'olap']);
```

#### `has`[​](#functions-example-has "Direct link to functions-example-has")

Array function [has](/docs/sql-reference/functions/array-functions#has) matches against a single token in the array of strings.

Example:

```
SELECT count() FROM table WHERE has(array, 'clickhouse');
```

#### `mapContains`[​](#functions-example-mapcontains "Direct link to functions-example-mapcontains")