> ## Documentation Index > Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt > Use this file to discover all available pages before exploring further. > Quickly find search terms in text. # Full-text Search with Text Indexes 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 ```result theme={null} 1: The cat likes mice. 2: Mice are afraid of dogs. 3: I have two dogs and a cat. ``` The corresponding tokens are: ```result theme={null} 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: ```result theme={null} 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: ```result theme={null} 1: cat, likes, mice 2: mice, afraid, dogs 3: have, two, dogs, cat ``` A text index then (conceptually) contains this information: ```result theme={null} 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

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. Text indexes can be used with any ClickHouse version >= 26.2, regardless of the [compatibility](/docs/reference/settings/session-settings#compatibility) setting. To create a text index use the following syntax: ```sql title="Query" theme={null} CREATE TABLE table ( key UInt64, str String, 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)] [, postprocessor = expression(str)] [, support_phrase_search = 0 | 1 ] -- experimental -- 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/reference/data-types/string) and [FixedString](/docs/reference/data-types/fixedstring), * [Array(String)](/docs/reference/data-types/array) and [Array(FixedString)](/docs/reference/data-types/array), * [Map](/docs/reference/data-types/map) (via [mapKeys](/docs/reference/functions/regular-functions/tuple-map-functions#mapKeys) and [mapValues](/docs/reference/functions/regular-functions/tuple-map-functions#mapValues) functions), and * [JSON](/docs/reference/data-types/newjson) (via [JSONAllPaths](/docs/reference/functions/regular-functions/json-functions#JSONAllPaths) and [`JSONAllValues`](/docs/reference/functions/regular-functions/json-functions#JSONAllValues) functions). Columns of type [Nullable(T)](/docs/reference/data-types/nullable) and [LowCardinality()](/docs/reference/data-types/lowcardinality) are also supported, including `Array(Nullable(String or FixedString))`. Alternatively, to add a text index to an existing table: ```sql title="Query" theme={null} 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)] [, postprocessor = expression(str)] [, support_phrase_search = 0 | 1 ] -- experimental -- 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). ```sql title="Query" theme={null} ALTER TABLE table MATERIALIZE INDEX text_idx SETTINGS mutations_sync = 2; ``` To remove a text index, please run ```sql title="Query" theme={null} 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/reference/functions/regular-functions/splitting-merging-functions#splitByNonAlpha)). * `splitByString(S)` splits strings along certain user-defined separator strings `S` (see function [splitByString](/docs/reference/functions/regular-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/reference/functions/regular-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/reference/functions/regular-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/reference/functions/regular-functions/array-functions#array)). All available tokenizers are listed in [system.tokenizers](/docs/reference/system-tables/tokenizers). The `splitByString` tokenizer applies the split separators left-to-right. This can create ambiguities. For example, the separator strings `['%21', '%']` will cause `%21abc` to be tokenized as `['abc']`, whereas switching both separators strings `['%', '%21']` will output `['21abc']`. In the most cases, you want that matching prefers longer separators first. This can generally be done by passing the separator strings in order of descending length. If the separator strings happen to form a [prefix code](https://en.wikipedia.org/wiki/Prefix_code), they can be passed in arbitrary order. To understand how a tokenizer split the input string, you can use the [tokens](/docs/reference/functions/regular-functions/splitting-merging-functions#tokens) and [tokensForLikePattern](/docs/reference/functions/regular-functions/splitting-merging-functions#tokensForLikePattern) functions: Example: ```sql title="Query" theme={null} SELECT tokens('abc def', 'ngrams', 3); ``` ```result title="Response" theme={null} ['abc','bc ','c d',' de','def'] ``` *Working with non-ASCII inputs.* Text indexes can be built on top of text data in any language and character set. For non-ASCII text, the `asciiCJK` tokenizer is recommended as it correctly handles Unicode word boundaries including CJK characters. **Preprocessor argument (optional)**. The preprocessor refers to an expression which is applied to the input string before tokenization. Typical use cases for the preprocessor argument include 1. Lower/upper-casing, or case folding to enable case-insensitive matching, e.g., [lower](/docs/reference/functions/regular-functions/string-functions#lower), [lowerUTF8](/docs/reference/functions/regular-functions/string-functions#lowerUTF8), [caseFoldUTF8](/docs/reference/functions/regular-functions/string-functions#caseFoldUTF8). 2. UTF-8 normalization, e.g. [normalizeUTF8NFC](/docs/reference/functions/regular-functions/string-functions#normalizeUTF8NFC), [normalizeUTF8NFD](/docs/reference/functions/regular-functions/string-functions#normalizeUTF8NFD), [normalizeUTF8NFKC](/docs/reference/functions/regular-functions/string-functions#normalizeUTF8NFKC), [normalizeUTF8NFKD](/docs/reference/functions/regular-functions/string-functions#normalizeUTF8NFKD), [normalizeUTF8NFKCCasefold](/docs/reference/functions/regular-functions/string-functions#normalizeUTF8NFKCCasefold), [toValidUTF8](/docs/reference/functions/regular-functions/string-functions#toValidUTF8). 3. Removing or transforming unwanted characters or substrings, such as accents e.g. [extractTextFromHTML](/docs/reference/functions/regular-functions/string-functions#extractTextFromHTML), [substring](/docs/reference/functions/regular-functions/string-functions#substring), [idnaEncode](/docs/reference/functions/regular-functions/string-functions#idnaEncode), [translate](/docs/reference/functions/regular-functions/string-replace-functions#translate), [removeDiacriticsUTF8](/docs/reference/functions/regular-functions/string-functions#removeDiacriticsUTF8). The preprocessor expression must transform an input value of type [String](/docs/reference/data-types/string) or [FixedString](/docs/reference/data-types/fixedstring) to a value of the same type. If the text index was build on a column of type `Nullable(T)` or `LowCardinality(T)` column, then the preprocessor expression should accept nullable or low-cardinality values (i.e. not throw an exception). Examples: * `INDEX idx col TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(col))` * `INDEX idx col TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = substringIndex(col, '\n', 1))` * `INDEX idx col TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(extractTextFromHTML(col)))` * `INDEX idx col TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = removeDiacriticsUTF8(caseFoldUTF8(col)))` Also, the preprocessor expression must only reference the column or expression on top of which the text index is defined. Examples: * `INDEX idx lower(col) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = upper(lower(col)))` * `INDEX idx lower(col) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = concat(lower(col), lower(col)))` * Not allowed: `INDEX idx lower(col) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = concat(col, col))` Usage of non-deterministic functions is disallowed. Preprocessors are in principle equivalent to wrapping the index column or expression by the preprocessor expression. For example, the `lower` preprocessor in `INDEX idx col TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(col))` can be emulated by `INDEX idx lower(col) TYPE text(tokenizer = 'splitByNonAlpha')`. The latter form has the disadvantage that the emulated preprocessor is only applied if it matches the filter condition in the WHERE clause. For example, `WHERE hasAllTokens(lower(col), [...])` matches while `WHERE hasAllTokens(col, [...])` does not. For an optimal user experience, we therefore recommend using preprocessor expressions. Functions [hasToken](/docs/reference/functions/regular-functions/string-search-functions#hasToken), [hasAllTokens](/docs/reference/functions/regular-functions/string-search-functions#hasAllTokens), [hasAnyTokens](/docs/reference/functions/regular-functions/string-search-functions#hasAnyTokens), and [hasPhrase](/docs/reference/functions/regular-functions/string-search-functions#hasPhrase) use the preprocessor to first transform the search term before tokenizing it. Note that because the preprocessor is only applied on the text index path, results from these functions may differ between queries that use the text index and queries that do not (e.g. `SETTINGS use_skip_indexes = 0`). For example, ```sql title="Query" theme={null} CREATE TABLE table ( str String, INDEX idx str TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(str)) ) ENGINE = MergeTree ORDER BY tuple(); SELECT count() FROM table WHERE hasToken(str, 'Foo'); ``` is equivalent to: ```sql title="Query" theme={null} CREATE TABLE table ( str String, INDEX idx lower(str) TYPE text(tokenizer = 'splitByNonAlpha') ) ENGINE = MergeTree ORDER BY tuple(); SELECT count() FROM table WHERE hasToken(str, lower('Foo')); ``` In this case, the preprocessor expression transforms the array elements individually. Example: ```sql title="Query" theme={null} CREATE TABLE table ( arr Array(String), INDEX idx arr TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(arr)) -- This is not legal: INDEX idx_illegal arr TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = arraySort(arr)) ) ENGINE = MergeTree ORDER BY tuple(); SELECT count() FROM tab WHERE hasAllTokens(arr, 'foo'); ``` To define a preprocessor in a text index on build [Map](/docs/reference/data-types/map)-type columns, users need to decide if the index is build on the map keys or values. Example: ```sql title="Query" theme={null} CREATE TABLE table ( map Map(String, String), INDEX idx mapKeys(map) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(mapKeys(map))) ) ENGINE = MergeTree ORDER BY tuple(); SELECT count() FROM tab WHERE hasAllTokens(mapKeys(map), 'foo'); ``` **Postprocessor argument (optional)**. The postprocessor refers to an expression which is applied to each output token after tokenization. Unlike the preprocessor, which transforms the entire input string before the tokenizer splits it into tokens, the postprocessor operates on the tokens themselves, one at a time. This is the natural place for transformations that are inherently token-level. Typical use cases for the postprocessor argument include: 1. **Filtering stop words (extremely frequent tokens)**. Very common tokens such as "the", "a", and "is" carry little search relevancy and inflate the index. You can use the postprocessor to discard them by converting them to empty tokens — empty tokens are ignored, i.e., not added to the index. Example: `if(str IN ('the', 'a', 'an', 'of', 'in', 'is', 'it'), '', str)` 2. **Timestamp removal**. Log lines often begin with or contain a structured timestamp such as `2024-01-15T10:23:45`. Indexing timestamp tokens bloats the index with strings that carry no search relevance. There are two complementary approaches to ignore timestamps: * **Postprocessor approach**: use the `splitByString` tokenizer (whitespace split) so that the entire timestamp becomes a single token, then use `parseDateTimeOrNull` to detect and drop it. Example: `if(isNull(parseDateTimeOrNull(str, '%Y-%m-%dT%H:%i:%S')), str, '')` For timestamps with timezone offsets or fractional seconds, use `parseDateTimeBestEffortOrNull(str)` without an explicit format string. * **Preprocessor approach**: strip the timestamp from the full log line *before* tokenization with a regular expression. Example: `replaceRegexpAll(str, '^[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2} ', '')` This works with any tokenizer and is more efficient as timestamp characters are never tokenized. Both approaches can be combined: the preprocessor strips the timestamp while the postprocessor normalizes or filters the remaining tokens (e.g., lowercase + drop severity words like `ERROR` or `INFO`). 3. **Stemming**. Mapping each token to its stem improves search recall by matching morphological variants that share the same root. For example, with English stemming "running", "runs", and "run" all stem to "run", so a query for any of these variants matches all of them. ClickHouse provides a built-in [stem](/docs/reference/functions/regular-functions/nlp-functions#stem) function for several languages. Example: `stem(str, 'en')` 4. **Case normalization**. Lower- or upper-casing tokens to enable case-insensitive matching, e.g. [lower](/docs/reference/functions/regular-functions/string-functions#lower), [lowerUTF8](/docs/reference/functions/regular-functions/string-functions#lowerUTF8). For lower-and upper casing, we recommend a preprocessor instead of a postprocessor. The postprocessor expression transforms tokens of type [String](/docs/reference/data-types/string) to tokens of the same type. Also, the postprocessor expression must only reference the column or expression on top of which the text index is defined. When the column is of type `Array(String)`, the postprocessor still operates on individual tokens as plain `String` values. Usage of non-deterministic functions is disallowed. The postprocessor is applied to each generated token during index build (for the `array` tokenizer, each array element is a token). At query time, the behavior depends on the function: * For `hasToken`, `hasAllTokens`, `hasAnyTokens`, and `hasPhrase` (with any supported tokenizer): the postprocessor is applied to both the haystack tokens and the search needle, enabling fully normalized matching (e.g., case-insensitive search). For `hasPhrase`, the postprocessed tokens are positioned densely, so a token the postprocessor drops leaves no positional gap and the phrase still matches across it — e.g. with a stop-word postprocessor that drops `the`, `hasPhrase(col, 'see cat')` matches a document `see the cat`. * For all other functions (`=`, `IN`, `has`, `hasAny`, `hasAll`, `mapContains*`): only the search needle is postprocessed for the index-hint lookup; the row-level predicate still compares against the original column values. Examples: * Remove stop words using a postprocessor expression: ```sql theme={null} CREATE TABLE table ( str String, INDEX idx(str) TYPE text( tokenizer = 'splitByNonAlpha', postprocessor = if(str IN ('the', 'a', 'an', 'of', 'in', 'is', 'it'), '', str) ) ) ENGINE = MergeTree ORDER BY tuple(); ``` * Remove timestamps using a postprocessor expression: ```sql theme={null} -- Log lines: '2024-01-15T10:23:45 ERROR connection failed' -- The splitByString tokenizer (default: whitespace) keeps the full timestamp as one token. -- parseDateTimeOrNull detects and drops it; non-timestamp words are kept. CREATE TABLE logs ( id UInt64, line String, INDEX idx(line) TYPE text( tokenizer = 'splitByString', postprocessor = if(isNull(parseDateTimeOrNull(line, '%Y-%m-%dT%H:%i:%S')), line, '') ) ) ENGINE = MergeTree ORDER BY id; -- Only message-level words are indexed; timestamp tokens are not stored. SELECT count() FROM logs WHERE hasAllTokens(line, ['ERROR']); -- fast index lookup SELECT count() FROM logs WHERE hasAllTokens(line, ['2024-01-15T10:23:45']); -- returns 0: token was never indexed ``` * Remove timestamps using a preprocessor expression: ```sql theme={null} -- The preprocessor strips the ISO timestamp prefix before tokenization. -- Any tokenizer can be used; timestamp characters are never seen by the tokenizer. CREATE TABLE logs ( id UInt64, line String, INDEX idx(line) TYPE text( tokenizer = 'splitByNonAlpha', preprocessor = replaceRegexpAll(line, '^[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2} ', '') ) ) ENGINE = MergeTree ORDER BY id; ``` * Remove timestamps using a combined preprocessor and postprocessor expression: ```sql theme={null} -- Preprocessor strips the timestamp, then lowercases the remainder. -- Postprocessor drops the severity word (error, info, warn, debug) after tokenization. -- Result: only substantive message words are stored in the index. CREATE TABLE logs ( id UInt64, line String, INDEX idx(line) TYPE text( tokenizer = 'splitByNonAlpha', preprocessor = lower(replaceRegexpAll(line, '^[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2} ', '')), postprocessor = if(line IN ('error', 'info', 'warn', 'warning', 'debug', 'critical'), '', line) ) ) ENGINE = MergeTree ORDER BY id; -- Example log line: '2024-01-15T10:23:45 ERROR connection failed' -- After preprocessor: 'error connection failed' -- After tokenization: ['error', 'connection', 'failed'] -- After postprocessor: ['connection', 'failed'] ← 'error' dropped as severity word SELECT count() FROM logs WHERE hasAllTokens(line, ['connection']); ``` * Stem tokens using a postprocessor expression: ```sql theme={null} CREATE TABLE table ( str String, INDEX idx(str) TYPE text( tokenizer = 'splitByNonAlpha', postprocessor = stem(str, 'en') ) ) ENGINE = MergeTree ORDER BY tuple(); -- The query token 'running' is stemmed to 'run' before the lookup, -- matching rows that contain 'run', 'runs', 'ran', 'running', etc. SELECT count() FROM table WHERE hasAllTokens(str, ['running']); ``` **Function support**. For predicates that consult the text index, the preprocessor and postprocessor are applied to the search value before the granule-level check so that the index lookup uses the same tokens that were stored at index build. For most functions (`=`, `IN`, `startsWith`, `endsWith`, `LIKE`, `mapContains*`), the text index is used only to skip irrelevant data blocks; ClickHouse still verifies each surviving row using the original predicate against the original column data. For token search functions (`hasToken`, `hasAllTokens`, `hasAnyTokens`), the text index is the primary evaluation path: ClickHouse normalizes the needle through the same preprocessor, tokenizer, and postprocessor that were applied at index build time, and uses this normalized form for both indexed and non-indexed table parts. With a postprocessor, the haystack tokens are also normalized at query time (for any tokenizer, not only `array`), so both sides of the comparison are consistently transformed and the result does not depend on whether the index is read directly (setting `query_plan_direct_read_from_text_index`) or whether a given part has a materialized index — e.g. enabling case-insensitive matching for `hasAllTokens(col, ['FOO'])` with a `lower` postprocessor. Without `support_phrase_search`, `hasPhrase` uses the index only as a hint and verifies each surviving row with the original predicate; a postprocessor additionally normalizes both the phrase and the haystack tokens the same way, so the result is independent of the read path, and tokens the postprocessor drops do not break phrase adjacency. With `support_phrase_search = 1`, `hasPhrase` uses exact direct reads (still applying the postprocessor, if any). Search tokens that the postprocessor maps to an empty string are ignored, i.e. treated as absent from the search phrase. | Function | Supports a preprocessor | Compatible tokenizers | Supports a postprocessor | | ------------------------------------------------------------------------------------------------------- | ------------------------------------ | -------------------------------------------------------- | ------------------------ | | `=` | yes | all | yes | | `IN` | yes | all | yes | | [hasToken](/docs/reference/functions/regular-functions/string-search-functions#hasToken) | yes | all (designed for `splitByNonAlpha`) | yes | | [hasAnyTokens(col, str)](/docs/reference/functions/regular-functions/string-search-functions#hasAnyTokens) | yes | all | yes | | [hasAllTokens(col, str)](/docs/reference/functions/regular-functions/string-search-functions#hasAllTokens) | yes | all | yes | | [hasAnyTokens(col, arr)](/docs/reference/functions/regular-functions/string-search-functions#hasAnyTokens) | no (array elements are tokens as-is) | all | yes | | [hasAllTokens(col, arr)](/docs/reference/functions/regular-functions/string-search-functions#hasAllTokens) | no (array elements are tokens as-is) | all | yes | | [hasPhrase](/docs/reference/functions/regular-functions/string-search-functions#hasPhrase) | yes | `splitByNonAlpha`, `splitByString`, `ngrams`, `asciiCJK` | yes | | [startsWith](/docs/reference/functions/regular-functions/string-functions#startsWith) | yes | `splitByNonAlpha`, `ngrams`, `sparseGrams`, `asciiCJK` | yes | | [endsWith](/docs/reference/functions/regular-functions/string-functions#endsWith) | yes | `splitByNonAlpha`, `ngrams`, `sparseGrams`, `asciiCJK` | yes | | [like](/docs/reference/functions/regular-functions/string-search-functions#like) | yes¹ | `splitByNonAlpha`, `ngrams`, `sparseGrams`, `asciiCJK`¹ | yes¹ | | [match](/docs/reference/functions/regular-functions/string-search-functions#match) | yes¹ | `splitByNonAlpha`, `ngrams`, `sparseGrams`, `asciiCJK`¹ | yes¹ | | [ilike](/docs/reference/functions/regular-functions/string-search-functions#like) | yes² (`lower`/`upper` only) | `splitByNonAlpha`, `array`² | no² | | [mapContainsKey](/docs/reference/functions/regular-functions/tuple-map-functions#mapContainsKey) | yes | all | yes | | [mapContainsValue](/docs/reference/functions/regular-functions/tuple-map-functions#mapContainsValue) | yes | all | yes | | [mapContainsKeyLike](/docs/reference/functions/regular-functions/tuple-map-functions#mapContainsKeyLike) | yes | `splitByNonAlpha`, `ngrams`, `sparseGrams`, `asciiCJK` | yes | | [mapContainsValueLike](/docs/reference/functions/regular-functions/tuple-map-functions#mapContainsValueLike) | yes | `splitByNonAlpha`, `ngrams`, `sparseGrams`, `asciiCJK` | yes | | [has](/docs/reference/functions/regular-functions/array-functions#has) | yes | `array` | yes | | [hasAny](/docs/reference/functions/regular-functions/array-functions#hasAny) | yes | `array` | yes | | [hasAll](/docs/reference/functions/regular-functions/array-functions#hasAll) | yes | `array` | yes | ¹ `LIKE` and `match` use direct read as a hint for the listed tokenizers, otherwise they fall back to brute-force scan. `LIKE` additionally supports a *direct read (without hint)* (enabled via `use_text_index_like_evaluation_by_dictionary_scan`) for `splitByNonAlpha` and `array` tokenizers without preprocessor or postprocessor. ² `ILIKE` is only supported via direct read (without hint) (`use_text_index_like_evaluation_by_dictionary_scan = 1`, `splitByNonAlpha` or `array` tokenizer). There is no fallback to using the index as a hint: if the setting is disabled or the tokenizer is not in the supported set, the index is not used for `ILIKE`. The preprocessor, if present, must be `lower` or `upper`; postprocessors are not supported. **Experimental: Support phrase search argument (optional)**. Experimental parameter `support_phrase_search` (default: `0`) controls whether the index stores token positions. When set to `1`, the index additionally stores positional data (in a `.pos` file) which enables exact phrase matching via direct reads for the [`hasPhrase`](#functions-example-hasphrase) function. Storing positions increases the on-disk size of the index and the write cost, so it is opt-in. The on-disk format is not yet stable, so this parameter is experimental and may change in a future release. Creating an index with `support_phrase_search = 1` therefore requires the MergeTree setting [`allow_experimental_text_index_phrase_search`](/docs/reference/settings/merge-tree-settings#allow_experimental_text_index_phrase_search) to be enabled. Set `support_phrase_search = 0` (the default) to keep the posting-list-only storage; text indexes created without this argument remain position-less. This argument is experimental and should only be used for testing. Set MergeTree setting [`allow_experimental_text_index_phrase_search`](/docs/reference/settings/merge-tree-settings#allow_experimental_text_index_phrase_search) to enable storing positions.
Optional advanced parameters The default values of the following advanced parameters will work well in virtually all situations. We do not recommend changing them. Optional parameter `dictionary_block_size` (default: 512) specifies the size of dictionary blocks in rows. Optional parameter `dictionary_block_frontcoding_compression` (default: 1) specifies if the dictionary blocks use front coding as compression. Optional parameter `posting_list_block_size` (default: 1048576) specifies the size of posting list blocks in rows. Optional parameter `posting_list_codec` (default: `none`) specifies the codec for posting list: * `none` - the posting lists are stored without additional compression. * `bitpacking` - apply [differential (delta) coding](https://en.wikipedia.org/wiki/Delta_encoding), followed by [bit-packing](https://dev.to/madhav_baby_giraffe/bit-packing-the-secret-to-optimizing-data-storage-and-transmission-m70) (each within blocks of fixed-size). Slows down SELECT queries, not recommended at the moment. The advanced parameters above can alternatively be set at the table level through the corresponding MergeTree settings: [`text_index_dictionary_block_size`](/docs/reference/settings/merge-tree-settings#text_index_dictionary_block_size), [`text_index_dictionary_block_frontcoding_compression`](/docs/reference/settings/merge-tree-settings#text_index_dictionary_block_frontcoding_compression), [`text_index_posting_list_block_size`](/docs/reference/settings/merge-tree-settings#text_index_posting_list_block_size), and [`text_index_posting_list_codec`](/docs/reference/settings/merge-tree-settings#text_index_posting_list_codec). They apply to every text index of the table that does not specify the parameter explicitly. The main use case of the table-level settings is to change the index parameters of an existing table without dropping and re-creating the text index on all table parts. Changing a table-level setting applies the new parameters only to text indexes built for new parts; existing parts keep their current layout. An argument given in the index definition takes precedence over the table setting, for example: ```sql theme={null} CREATE TABLE table( s String, -- This index uses 'bitpacking', overriding the table-level default below: INDEX idx_a s TYPE text(tokenizer = 'splitByNonAlpha', posting_list_codec = 'bitpacking'), -- This index inherits 'none' from the table setting: INDEX idx_b lower(s) TYPE text(tokenizer = 'splitByNonAlpha')) ENGINE = MergeTree() ORDER BY tuple() SETTINGS text_index_posting_list_codec = 'none'; ```
*Index granularity.* Text indexes are implemented within ClickHouse as a type of [skip indexes](/docs/reference/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: ```sql title="Query" theme={null} 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 title="Response" theme={null} ┌─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 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. 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 and postprocessor 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 or postprocessor support).

Supported functions

The text index can be used if text functions are used in the `WHERE` clause or `PREWHERE` clauses: ```sql theme={null} SELECT [...] FROM [...] WHERE string_search_function(column_with_text_index) ```

`=`

`=` ([equals](/docs/reference/functions/regular-functions/comparison-functions#equals)) matches the entire given search term. Example: ```sql theme={null} SELECT * from table WHERE str = 'Hello'; ```

`IN`

`IN` ([in](/docs/reference/functions/regular-functions/in-functions)) is similar to `equals` but matches all search terms. Example: ```sql theme={null} SELECT * from table WHERE str IN ('Hello', 'World'); ``` `NOT IN` (`notIn`) is not supported by the text index.

`LIKE` and `match`

These functions currently use the text index for filtering only if the index tokenizer is either `splitByNonAlpha`, `ngrams` or `sparseGrams`. `NOT LIKE` (`notLike`) is not supported by the text index. In order to use `LIKE` ([like](/docs/reference/functions/regular-functions/string-search-functions#like)) and the [match](/docs/reference/functions/regular-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: ```sql theme={null} 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: ```sql theme={null} 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.

`multiSearchAny` and `multiMatchAny`

[multiSearchAny](/docs/reference/functions/regular-functions/string-search-functions#multiSearchAny) and its UTF-8 variant [multiSearchAnyUTF8](/docs/reference/functions/regular-functions/string-search-functions#multiSearchAnyUTF8) test whether any of several literal substrings occurs in the haystack, and [multiMatchAny](/docs/reference/functions/regular-functions/string-search-functions#multiMatchAny) tests whether any of several regular expressions matches. These functions use the text index under the same conditions as `LIKE` and `match` (see above): ClickHouse must be able to extract complete tokens from each needle, and the list of needles must be constant. A granule is read if any needle may be present in it. For `multiMatchAny`, if a single pattern cannot be reduced to a token requirement (for example `.*`, which matches any document), the text index cannot be used and the query falls back to a full scan. As with `LIKE` and `match`, substring and regular-expression search work best with the `ngrams` and `sparseGrams` tokenizers. These tokenizers index overlapping character n-grams, so a needle is decomposed into n-grams that are present in the index wherever the needle occurs as a substring, regardless of whether it starts or ends in the middle of a word. A needle can therefore be used as-is, as long as it is at least as long as the n-gram size. Example for the text index with `ngrams` tokenizer: ```sql theme={null} SELECT count() FROM table WHERE multiSearchAny(comment, ['clickhouse', 'support']); ``` The `splitByNonAlpha` tokenizer, in contrast, only indexes complete tokens (whole words). Because a needle may begin or end in the middle of a word, ClickHouse drops the leading and trailing tokens of each needle, so the index can prune granules only using complete tokens. To make substring and regular-expression search use the index with `splitByNonAlpha`, surround each needle with separator characters (such as spaces) so that it forms one or more complete tokens. Example for the text index with `splitByNonAlpha` tokenizer: ```sql theme={null} SELECT count() FROM table WHERE multiSearchAny(comment, [' clickhouse ', ' support ']); ```

`startsWith` and `endsWith`

Similar to `LIKE`, functions [startsWith](/docs/reference/functions/regular-functions/string-functions#startsWith) and [endsWith](/docs/reference/functions/regular-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. When a text index uses a postprocessor, these functions can still use the index in Hint mode if the extracted hint tokens remain non-empty after normalization. If normalization drops all hint tokens, the index is not used for that predicate. Example for the text index with `splitByNonAlpha` tokenizer: ```sql theme={null} 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: ```sql theme={null} startsWith(comment, 'clickhouse supports ')` ``` Similarly, `endsWith` should be used with a leading space: ```sql theme={null} SELECT count() FROM table WHERE endsWith(comment, ' olap engine'); ```

`hasToken`

`hasToken` has certain pitfalls when used for lookups in text indexes with non-`splitByNonAlpha` tokenizers and/or preprocessor/postprocessor expressions. We recommend using `hasAnyTokens` and `hasAllTokens` instead. The case-insensitive variants `hasTokenCaseInsensitive` and `hasTokenCaseInsensitiveOrNull` are not text-index-aware — they always run as a full row scan even on text-indexed columns. For case-insensitive matching, use a `lower(...)` preprocessor or postprocessor and combine it with `hasToken` / `hasAllTokens` / `hasAnyTokens`. Function [hasToken](/docs/reference/functions/regular-functions/string-search-functions#hasToken) matches 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: ```sql theme={null} SELECT count() FROM table WHERE hasToken(comment, 'clickhouse'); ```

`hasAnyTokens` and `hasAllTokens`

Functions [hasAnyTokens](/docs/reference/functions/regular-functions/string-search-functions#hasAnyTokens) and [hasAllTokens](/docs/reference/functions/regular-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: ```sql theme={null} -- 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']); ```

`hasPhrase`

Function [hasPhrase](/docs/reference/functions/regular-functions/string-search-functions#hasPhrase) matches against a phrase: all tokens must appear consecutively and in the same order as in the search string. Unlike `hasAllTokens`, which only requires all tokens to be present somewhere, `hasPhrase` requires them to appear as a consecutive sequence. The search phrase is tokenized using the same tokenizer configured for the index column. When the text index uses a postprocessor, the search phrase is normalized before the index lookup as well. Note that the function requires one of the `splitByNonAlpha`, `splitByString`, `ngrams`, or `asciiCJK` tokenizers. Example: ```sql theme={null} -- Matches: 'clickhouse' and 'olap' must appear consecutively in that order SELECT count() FROM table WHERE hasPhrase(comment, 'clickhouse olap'); -- Does NOT match a row containing 'olap clickhouse' (wrong order) -- Does NOT match a row containing 'clickhouse fast olap' (non-consecutive) ```

`has`

Array function [has](/docs/reference/functions/regular-functions/array-functions#has) matches against a single token in the array of strings. Example: ```sql theme={null} SELECT count() FROM table WHERE has(array, 'clickhouse'); ```

`hasAny` and `hasAll`

Array functions [hasAny](/docs/reference/functions/regular-functions/array-functions#hasAny) and [hasAll](/docs/reference/functions/regular-functions/array-functions#hasAll) test whether the indexed array column contains any or all of a constant set of needle strings. Example: ```sql theme={null} SELECT count() FROM table WHERE hasAny(tags, ['clickhouse', 'olap']); SELECT count() FROM table WHERE hasAll(tags, ['clickhouse', 'olap']); ```

`mapContains`

Function [mapContains](/docs/reference/functions/regular-functions/tuple-map-functions#mapContainsKey) (an alias of `mapContainsKey`) matches against tokens extracted from the searched string in the keys of a map. The behaviour is similar to the `equals` function with a `String` column. The text index is only used if it was created on a `mapKeys(map)` expression. Example: ```sql theme={null} SELECT count() FROM table WHERE mapContainsKey(map, 'clickhouse'); -- OR SELECT count() FROM table WHERE mapContains(map, 'clickhouse'); ```

`mapContainsValue`

Function [mapContainsValue](/docs/reference/functions/regular-functions/tuple-map-functions#mapContainsValue) matches against tokens extracted from the searched string in the values of a map. The behaviour is similar to the `equals` function with a `String` column. The text index is only used if it was created on a `mapValues(map)` expression. Example: ```sql theme={null} SELECT count() FROM table WHERE mapContainsValue(map, 'clickhouse'); ```

`mapContainsKeyLike` and `mapContainsValueLike`

The functions [mapContainsKeyLike](/docs/reference/functions/regular-functions/tuple-map-functions#mapContainsKeyLike) and [mapContainsValueLike](/docs/reference/functions/regular-functions/tuple-map-functions#mapContainsValueLike) match a pattern against all keys or values (respectively) of a map. Example: ```sql theme={null} SELECT count() FROM table WHERE mapContainsKeyLike(map, '% clickhouse %'); SELECT count() FROM table WHERE mapContainsValueLike(map, '% clickhouse %'); ```

`operator[]`

Access [operator\[\]](/docs/reference/operators/index#access-operators) can be used with the text index to filter out keys and values. The text index is only used if it is created on `mapKeys(map)` or `mapValues(map)` expressions, or both. Example: ```sql theme={null} SELECT count() FROM table WHERE map['engine'] = 'clickhouse'; ``` See the following examples for using columns of type `Array(T)` and `Map(K, V)` with the text index.

Indexing Array(String) columns

Imagine a blogging platform, where authors categorize their blog posts using keywords. We like users to discover related content by searching for or clicking on topics. Consider this table definition: ```sql theme={null} CREATE TABLE posts ( post_id UInt64, title String, content String, keywords Array(String) ) ENGINE = MergeTree ORDER BY (post_id); ``` Without a text index, finding posts with a specific keyword (e.g. `clickhouse`) requires scanning all entries: ```sql theme={null} SELECT count() FROM posts WHERE has(keywords, 'clickhouse'); -- slow full-table scan - checks every keyword in every post ``` As the platform grows, this becomes increasingly slow because the query must examine every keywords array in every row. To overcome this performance issue, we define a text index for column `keywords`: ```sql theme={null} ALTER TABLE posts ADD INDEX keywords_idx(keywords) TYPE text(tokenizer = splitByNonAlpha); ALTER TABLE posts MATERIALIZE INDEX keywords_idx; -- Don't forget to rebuild the index for existing data ```

Indexing Map columns

In many observability use cases, log messages are split into "components" and stored as appropriate data types, e.g. date time for the timestamp, enum for the log level etc. Metrics fields are best stored as key-value pairs. Operations teams need to efficiently search through logs for debugging, security incidents, and monitoring. Consider this logs table: ```sql theme={null} CREATE TABLE logs ( id UInt64, timestamp DateTime, message String, attributes Map(String, String) ) ENGINE = MergeTree ORDER BY (timestamp); ``` Without a text index, searching through [Map](/docs/reference/data-types/map) data requires full table scans: ```sql theme={null} -- Finds all logs with rate limiting data: SELECT * FROM logs WHERE has(mapKeys(attributes), 'rate_limit'); -- slow full-table scan -- Finds all logs from a specific IP: SELECT * FROM logs WHERE has(mapValues(attributes), '192.168.1.1'); -- slow full-table scan ``` As log volume grows, these queries become slow. The solution is creating a text index for the [Map](/docs/reference/data-types/map) keys and values. Use [mapKeys](/docs/reference/functions/regular-functions/tuple-map-functions#mapKeys) to create a text index when you need to find logs by field names or attribute types: ```sql theme={null} ALTER TABLE logs ADD INDEX attributes_keys_idx mapKeys(attributes) TYPE text(tokenizer = array); ALTER TABLE posts MATERIALIZE INDEX attributes_keys_idx; ``` Use [mapValues](/docs/reference/functions/regular-functions/tuple-map-functions#mapValues) to create a text index when you need to search within the actual content of attributes: ```sql theme={null} ALTER TABLE logs ADD INDEX attributes_vals_idx mapValues(attributes) TYPE text(tokenizer = array); ALTER TABLE posts MATERIALIZE INDEX attributes_vals_idx; ``` Example queries: ```sql theme={null} -- Find all rate-limited requests: SELECT * FROM logs WHERE mapContainsKey(attributes, 'rate_limit'); -- fast -- Finds all logs from a specific IP: SELECT * FROM logs WHERE has(mapValues(attributes), '192.168.1.1'); -- fast -- Finds all logs where any attribute includes an error: SELECT * FROM logs WHERE mapContainsValueLike(attributes, '% error %'); -- fast ```

Indexing JSON columns

Text indexes can be used with `JSON` columns in three ways: 1. **Indexes on specific subcolumns** — create a text index on a known JSON path, just like on a regular column. This indexes the *values* at that path. 2. **Path-based indexes with [JSONAllPaths](/docs/reference/functions/regular-functions/json-functions#JSONAllPaths)** — indexes *all paths* present in each granule to skip granules that cannot contain the queried path. Similar to `Map` columns. 3. **Value-based indexes with [JSONAllValues](/docs/reference/functions/regular-functions/json-functions#JSONAllValues)** — indexes *all values* across all JSON paths to accelerate full-text search on any JSON subcolumn with a single index.

Indexes on specific subcolumns

You can create a skip index on any JSON subcolumn using the same syntax as for regular columns. There are two ways to reference a JSON subcolumn in an index expression: * **Typed path** declared in the JSON type hint — access by name directly: `json.a`. * **Dynamic path** with explicit cast — use the `::` cast syntax: `json.b::String`. Example index definition: ```sql title="Query" theme={null} CREATE TABLE sensor_data ( data JSON(sensor_id String), INDEX idx_sensor data.sensor_id TYPE text(tokenizer = splitByNonAlpha), INDEX idx_location data.location::String TYPE text(tokenizer = splitByNonAlpha) ) ENGINE = MergeTree ORDER BY tuple() SETTINGS index_granularity = 1; INSERT INTO sensor_data SELECT toJSONString(map('sensor_id', 'id_' || number , 'location', 'room_' || toString(number))) FROM numbers(4); INSERT INTO sensor_data SELECT toJSONString(map('sensor_id', 'id_' || number, 'location', 'room_' || toString(number))) FROM numbers(4, 4); ``` Example query: ```sql title="Query" theme={null} EXPLAIN indexes = 1 SELECT * FROM sensor_data WHERE data.sensor_id = 'id_5'; ``` ```text title="Response" theme={null} ... Indexes: Skip Name: idx_sensor Description: text Condition: (mode: All; tokens: ["5", "id"]) Parts: 1/2 Granules: 1/8 ``` Example query: ```sql title="Query" theme={null} EXPLAIN indexes = 1 SELECT * FROM sensor_data WHERE data.location::String = 'room_5'; ``` ```text title="Response" theme={null} ... Indexes: Skip Name: idx_location Description: text Condition: (mode: All; tokens: ["5", "room"]) Parts: 1/2 Granules: 1/8 ```

Path-based indexes with JSONAllPaths

Similar to `Map` columns, text indexes can be created on [JSON](/docs/reference/data-types/newjson) columns using [`JSONAllPaths`](/docs/reference/functions/regular-functions/json-functions#JSONAllPaths). The index stores the set of JSON paths present in each granule and uses them to skip granules where a queried path is absent. Example index definition: ```sql title="Query" theme={null} CREATE TABLE events ( data JSON, INDEX idx JSONAllPaths(data) TYPE text(tokenizer = array) ) ENGINE = MergeTree ORDER BY tuple(); INSERT INTO events VALUES ('{"user": {"name": "Alice"}, "action": "login"}'); INSERT INTO events VALUES ('{"metric": {"cpu": 0.95}, "host": "srv1"}'); ``` You can use `EXPLAIN indexes = 1` to verify that the skip index is being used. When a path exists only in one part, the index skips the other part. Example: ```sql title="Query" theme={null} EXPLAIN indexes = 1 SELECT * FROM events WHERE data.user.name = 'Alice'; ``` ```text title="Response" theme={null} ... Indexes: Skip Name: idx Description: text Condition: (mode: All; tokens: ["user.name"]) Parts: 1/2 Granules: 1/2 ``` When a path does not exist in any part, all parts and granules are skipped. Example: ```sql title="Query" theme={null} EXPLAIN indexes = 1 SELECT * FROM events WHERE data.nonexistent = 1; ``` ```text title="Response" theme={null} ... Indexes: Skip Name: idx Description: text Condition: (mode: All; tokens: ["nonexistent"]) Parts: 0/2 Granules: 0/2 ``` `IS NOT NULL` also uses the index — it skips granules where the path is absent (since the value would be `NULL`): Example: ```sql title="Query" theme={null} EXPLAIN indexes = 1 SELECT * FROM events WHERE data.user.name IS NOT NULL; ``` ```text title="Response" theme={null} ... Indexes: Skip Name: idx Description: text Condition: (mode: All; tokens: ["user.name"]) Parts: 1/2 Granules: 1/2 ```

Value-based indexes with JSONAllValues

Text indexes can be used to accelerate searches on [JSON](/docs/reference/data-types/newjson) columns via function [`JSONAllValues`](/docs/reference/functions/regular-functions/json-functions#JSONAllValues). `JSONAllValues` returns all values from a JSON column as `Array(String)`. Values of non-string datatypes (e.g. integers and arrays) are converted to their text representation. A text index build using `JSONAllValues` indexes these text representations across all JSON paths in each row. This index can then accelerate queries that filter on individual JSON subcolumns. When a query filters on a specific subcolumn (e.g. `data.user_name = 'alice'`), the text index can quickly skip rows (and granules) that do not contain the search tokens in any of their JSON values. The index may produce false positives when different JSON paths contain the same tokens. For example, if row 1 has `{"a": "hello", "b": "world"}` and a query searches for `data.a = 'world'`, the text index cannot distinguish that `world` belongs to path `b`, not `a`. In such cases, the index will not skip the row, and the filter on the actual column data will handle the final evaluation. This is the same behavior as with other text index use cases where the index acts as a fast pre-filter.
Creating the index
Example index definition: ```sql theme={null} CREATE TABLE events ( id UInt64, data JSON, INDEX json_idx JSONAllValues(data) TYPE text(tokenizer = splitByNonAlpha) ) ENGINE = MergeTree ORDER BY id; ```
Supported query patterns
Once the index is created, it can speed up queries on JSON subcolumns using the same functions as for `String` columns and function `equals` for all columns. Subcolumn access: ```sql theme={null} SELECT * FROM events WHERE data.user_name = 'alice'; SELECT * FROM events WHERE data.message LIKE '% error %'; SELECT * FROM events WHERE startsWith(data.status, 'fail'); SELECT * FROM events WHERE hasToken(data.title, 'clickhouse'); ``` Subcolumn access with explicit `CAST`: ```sql theme={null} SELECT * FROM events WHERE hasAllTokens(data.message::String, 'connection timeout'); SELECT * FROM events WHERE data.status_code::UInt64 = 404; SELECT * FROM events WHERE has(data.tags::Array(String), 'bug') ``` `IN` operator: ```sql theme={null} SELECT * FROM events WHERE data.level IN ('error', 'critical'); ```

Phrase search

A regular text index search, for example ```sql theme={null} SELECT * FROM tab WHERE hasAllTokens(col, 'weather in Tokyo') ``` matches all rows that contain the given tokens in arbitrary order. In the example, row `While she stayed in Tokyo, the weather was great.` matches the filter. In contrast, phrase search means matching the tokens in the given order. For example, ```sql theme={null} SELECT * FROM tab WHERE hasPhrase(col, 'weather in Tokyo') ``` matches any row that contains the token sequence `weather in Tokyo` like `How is the weather in Tokyo?`? The text index accelerates phrase search by intersecting the posting lists for all tokens in the phrase to identify candidate granules. Within those granules, ClickHouse then verifies exact token adjacency. This process is relatively costly and slower than regular text search queries. To speed phrase search queries up, please enable position storage in the text index (see `Optional parameters` above). `hasPhrase` can be used together with tokenizers `splitByNonAlpha`, `splitByString`, `ngrams`, and `asciiCJK`. The given phrase string is tokenized using the index's tokenizer. Separator characters in the phrase are ignored: `hasPhrase(text, 'quick+brown')` is equivalent to `hasPhrase(text, 'quick brown')`, assuming `splitByNonAlpha` is used as tokenizer.

Example

```sql theme={null} CREATE TABLE tab ( id UInt32, text String, INDEX idx text TYPE text(tokenizer = splitByNonAlpha) ) ENGINE = MergeTree ORDER BY id; INSERT INTO tab VALUES (1, 'weather in New York'), (2, 'New weather in York'), (3, 'weather in New Orleans'); ``` ```sql title="Query" theme={null} SELECT id, text FROM tab WHERE hasPhrase(text, 'weather in New York'); ``` ```result title="Response" theme={null} ┌─id─┬─text────────────────┐ 1. │ 1 │ weather in New York │ └────┴─────────────────────┘ ``` Row 2 (`'New weather in York'`) does not match because the tokens are in the wrong order. Row 3 (`'weather in New Orleans'`) does not match because it does not contain the token `'York'`.

Performance Tuning

Direct read

Certain types of text queries can be speed up significantly by an optimization called "direct read". Example: ```sql theme={null} SELECT column_a, column_b, ... FROM [...] WHERE string_search_function(column_with_text_index) ``` The direct read optimization answers the query exclusively using the text index (i.e., text index lookups) without accessing the underlying text column. Text index lookups read relatively little data and are therefore much faster than usual skip indexes in ClickHouse (which do a skip index lookup, followed by loading and filtering remaining granules). Direct read is controlled by two settings: * Setting [query\_plan\_direct\_read\_from\_text\_index](/docs/reference/settings/session-settings#query_plan_direct_read_from_text_index) (true by default) which specifies if direct read is generally enabled. * Setting [use\_skip\_indexes\_on\_data\_read](/docs/reference/settings/session-settings#use_skip_indexes_on_data_read) was a prerequisite for direct read in ClickHouse versions \< 26.4. **Supported functions** The direct read optimization supports functions `hasToken`, `hasAllTokens`, and `hasAnyTokens`. If the text index is defined with an `array` tokenizer, direct read is also supported for functions `equals`, `has`, `hasAny`, `hasAll`, `mapContainsKey`, and `mapContainsValue`. These functions can also be combined by `AND`, `OR`, and `NOT` operators. The `WHERE` or `PREWHERE` clauses can also contain additional non-text-search-functions filters (for text columns or other columns) - in that case, the direct read optimization will still be used but less effective (it only applies to the supported text search functions). To understand a query utilizes direct read, run the query with `EXPLAIN PLAN actions = 1`. As an example, a query with disabled direct read ```sql theme={null} EXPLAIN PLAN actions = 1 SELECT count() FROM table WHERE hasToken(col, 'some_token') SETTINGS query_plan_direct_read_from_text_index = 0, -- disable direct read ``` returns ```text theme={null} [...] Filter ((WHERE + Change column names to column identifiers)) Filter column: hasToken(__table1.col, 'some_token'_String) (removed) Actions: INPUT : 0 -> col String : 0 COLUMN Const(String) -> 'some_token'_String String : 1 FUNCTION hasToken(col :: 0, 'some_token'_String :: 1) -> hasToken(__table1.col, 'some_token'_String) UInt8 : 2 [...] ``` whereas the same query run with `query_plan_direct_read_from_text_index = 1` ```sql theme={null} EXPLAIN PLAN actions = 1 SELECT count() FROM table WHERE hasToken(col, 'some_token') SETTINGS query_plan_direct_read_from_text_index = 1, -- enable direct read ``` returns ```text theme={null} [...] Expression (Before GROUP BY) Positions: Filter Filter column: __text_index_idx_hasToken_94cc2a813036b453d84b6fb344a63ad3 (removed) Actions: INPUT :: 0 -> __text_index_idx_hasToken_94cc2a813036b453d84b6fb344a63ad3 UInt8 : 0 [...] ``` The second EXPLAIN PLAN output contains a virtual column `__text_index___`. If this column is present, then direct read is used. If the WHERE filter clause only contains text search functions, the query can avoid reading the column data entirely and has the greatest performance benefit by direct read. However, even if the text column is accessed elsewhere in the query, direct read will still provide performance improvement. **Direct read as a hint** Direct read as a hint is based on the same principles as normal direct read, but instead adds an additional filter build from the text index data without removing the underlying text column. It is used for functions when reading only from the text index would produce false positives. Supported functions are: `like`, `startsWith`, `endsWith`, `equals`, `has`, `hasPhrase`, `mapContainsKey`, and `mapContainsValue`. The additional filter can provide additional selectivity to restrict the result set in combination with other filters further, helping to reduce the amount of data read from other columns. Direct read as a hint is controlled by setting [query\_plan\_text\_index\_add\_hint](/docs/reference/settings/session-settings#query_plan_text_index_add_hint) (enabled by default). Example of query without hint: ```sql theme={null} EXPLAIN actions = 1 SELECT count() FROM table WHERE (col LIKE '%some-token%') AND (d >= today()) SETTINGS query_plan_text_index_add_hint = 0 FORMAT TSV ``` returns ```text theme={null} [...] Prewhere filter column: and(like(__table1.col, \'%some-token%\'_String), greaterOrEquals(__table1.d, _CAST(20440_Date, \'Date\'_String))) (removed) [...] ``` whereas the same query run with `query_plan_text_index_add_hint = 1` ```sql theme={null} EXPLAIN actions = 1 SELECT count() FROM table WHERE col LIKE '%some-token%' SETTINGS query_plan_text_index_add_hint = 1 ``` returns ```text theme={null} [...] Prewhere filter column: and(__text_index_idx_col_like_d306f7c9c95238594618ac23eb7a3f74, like(__table1.col, \'%some-token%\'_String), greaterOrEquals(__table1.d, _CAST(20440_Date, \'Date\'_String))) (removed) [...] ``` In the second EXPLAIN PLAN output, you can see that an additional conjunct (`__text_index_...`) has been added to the filter condition. Thanks to the [PREWHERE](/docs/reference/statements/select/prewhere) optimization, the filter condition is broken down into three separate conjuncts, which are applied in order of increasing computational complexity. For this query, the application order is `__text_index_...`, then `greaterOrEquals(...)`, and finally `like(...)`. This ordering enables skipping even more data granules than the granules skipped by the text index and the original filter, before reading the heavy columns used in the query after `WHERE` clause further reducing the amount of data to read.

LIKE/ILIKE queries

When a LIKE/ILIKE query pattern is `%%` and the text index tokenizer is `splitByNonAlpha` or `array`, ClickHouse leverages the inverted index to speed up LIKE/ILIKE queries significantly. To achieve that, ClickHouse scans the inverted index dictionary instead of a full-table scan to find the matching pattern. When the optimization is enabled, LIKE/ILIKE queries should be significantly faster than a full-table scan. However, when the pattern matches most dictionary tokens, the performance can be worse compared to a full-table scan. Luckily, there is a fallback mechanism to prevent that. The optimization is controlled by a setting: * [use\_text\_index\_like\_evaluation\_by\_dictionary\_scan](/docs/reference/settings/session-settings#use_text_index_like_evaluation_by_dictionary_scan) The fallback mechanism is controlled by two settings: * [text\_index\_like\_min\_pattern\_length](/docs/reference/settings/session-settings#text_index_like_min_pattern_length) * [text\_index\_like\_max\_postings\_to\_read](/docs/reference/settings/session-settings#text_index_like_max_postings_to_read) This optimization supports only functions `like` and `ilike`.

Caching

Different server-wide caches exist to buffer parts of the text index in memory (see section [Implementation Details](#implementation)): Currently, there are caches for the deserialized headers, tokens, and posting lists of the text index to reduce I/O. Use settings [use\_text\_index\_header\_cache](/docs/reference/settings/session-settings#use_text_index_header_cache), [use\_text\_index\_tokens\_cache](/docs/reference/settings/session-settings#use_text_index_tokens_cache), and [use\_text\_index\_postings\_cache](/docs/reference/settings/session-settings#use_text_index_postings_cache) to disable reading and writing from/to the individual caches by queries. To clear the caches, use statement [SYSTEM CLEAR TEXT INDEX CACHES](/docs/reference/statements/system#drop-text-index-caches) Please refer the following server settings to configure the caches.

Tokens cache settings

| Setting | Description | | ---------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------- | | [text\_index\_tokens\_cache\_policy](/docs/reference/settings/server-settings/settings#text_index_tokens_cache_policy) | Text index tokens cache policy name. | | [text\_index\_tokens\_cache\_size](/docs/reference/settings/server-settings/settings#text_index_tokens_cache_size) | Maximum cache size in bytes. | | [text\_index\_tokens\_cache\_max\_entries](/docs/reference/settings/server-settings/settings#text_index_tokens_cache_max_entries) | Maximum number of deserialized tokens in cache. | | [text\_index\_tokens\_cache\_size\_ratio](/docs/reference/settings/server-settings/settings#text_index_tokens_cache_size_ratio) | The size of the protected queue in the text index tokens cache relative to the cache's total size. |

Header cache settings

| Setting | Description | | ---------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------- | | [text\_index\_header\_cache\_policy](/docs/reference/settings/server-settings/settings#text_index_header_cache_policy) | Text index header cache policy name. | | [text\_index\_header\_cache\_size](/docs/reference/settings/server-settings/settings#text_index_header_cache_size) | Maximum cache size in bytes. | | [text\_index\_header\_cache\_max\_entries](/docs/reference/settings/server-settings/settings#text_index_header_cache_max_entries) | Maximum number of deserialized headers in cache. | | [text\_index\_header\_cache\_size\_ratio](/docs/reference/settings/server-settings/settings#text_index_header_cache_size_ratio) | The size of the protected queue in the text index header cache relative to the cache's total size. |

Posting lists cache settings

| Setting | Description | | -------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------- | | [text\_index\_postings\_cache\_policy](/docs/reference/settings/server-settings/settings#text_index_postings_cache_policy) | Text index postings cache policy name. | | [text\_index\_postings\_cache\_size](/docs/reference/settings/server-settings/settings#text_index_postings_cache_size) | Maximum cache size in bytes. | | [text\_index\_postings\_cache\_max\_entries](/docs/reference/settings/server-settings/settings#text_index_postings_cache_max_entries) | Maximum number of deserialized postings in cache. | | [text\_index\_postings\_cache\_size\_ratio](/docs/reference/settings/server-settings/settings#text_index_postings_cache_size_ratio) | The size of the protected queue in the text index postings cache relative to the cache's total size. |

Limitations

The text index currently has the following limitations: * The materialization of text indexes with a high number of tokens (e.g. 10 billion tokens) can consume significant amounts of memory. Text index materialization can happen directly (`ALTER TABLE MATERIALIZE INDEX `) or indirectly in part merges. * It is not possible to materialize text indexes on parts with more than 4.294.967.296 (= 2^32 = ca. 4.2 billion) rows. Without a materialized text index, queries fall back to slow brute-force search within the part. As a worst case estimation, assume a part contains a single column of type String and MergeTree setting `max_bytes_to_merge_at_max_space_in_pool` (default: 150 GB) was not changed. In this case, the situation happens if the column contains less than 29.5 characters per row on average. In practice, tables also contain other columns and the threshold is multiples times smaller than that (depending on the number, type and size of the other columns).

Text Indexes vs Bloom-Filter-Based Indexes

String predicates can be sped up using text indexes and bloom-filter-based based indexes (index type `bloom_filter`, `ngrambf_v1`, `tokenbf_v1`, `sparse_grams`), yet both are fundamentally different in their design and intended use cases: **Bloom filter indexes** * Are based on probabilistic data structures which may produce false positives. * Are only able to answer set membership questions, i.e. the column may contain token X vs. definitely does not contain X. * Store granule-level information to enable skipping coarse ranges during query execution. * Are hard to tune properly (see [here](/docs/reference/engines/table-engines/mergetree-family/mergetree#n-gram-bloom-filter) for an example). * Are rather compact (a few kilobytes or megabytes per part). **Text indexes** * Build a deterministic inverted index over tokens. No false positives are possible by the index itself. * Are specifically optimized for text search workloads. * Store row-level information which enables efficient term lookup. * Are rather large (dozens to hundreds of megabytes per part). Bloom-filter-based indexes support full-text search only as a "side effect": * They do not support advanced tokenization and preprocessing. * They do not support multi-token search. * They do not provide the performance characteristics expected from an inverted index. Text indexes, in contrast, are purpose-built for full-text search: * They provide tokenization and preprocessing * They provide efficient support for `hasAllTokens`, `LIKE`, `match`, and similar text-search functions. * They have significantly better scalability for large text corpora.

Implementation Details

Each text index consists of two (abstract) data structures: * a dictionary which maps each token to a postings list, and * a set of postings lists, each representing a set of row numbers. Text index is built for the whole part. Unlike other skip indexes, text index can be merged instead of rebuilt on merge of the data parts (see below). During index creation, three files are created (per part): **Dictionary blocks file (.dct)** The tokens in the text index are sorted and stored in dictionary blocks of 512 tokens each (the block size is configurable by parameter `dictionary_block_size`). A dictionary blocks file (.dct) consists all the dictionary blocks of all index granules in a part. **Index header file (.idx)** The index header file contains for each dictionary block the block's first token and its relative offset in the dictionary blocks file. This sparse index structure is similar to ClickHouse's [sparse primary key index](/docs/guides/clickhouse/data-modelling/sparse-primary-indexes)). **Postings lists file (.pst)** The posting lists for all tokens are laid out sequentially in the postings list file. To save space while still allowing fast intersection and union operations, the posting lists are stored as [roaring bitmaps](https://roaringbitmap.org/). If the posting list is larger than `posting_list_block_size`, it is split into multiple blocks that are stored sequentially to the postings lists file. **Positions file (.pos)** Optional, only if index argument `support_phrase_search = 1`. Stores the positions of the tokens within matching rows. **Merging of text indexes** When data parts are merged, the text index does not need to be rebuilt from scratch; instead, it can be merged efficiently in a separate step of the merge process. During this step, the sorted dictionaries of the text indexes of each input part are read and combined into a new unified dictionary. The row numbers in the postings lists are also recalculated to reflect their new positions in the merged data part, using a mapping of old to new row numbers that is created during the initial merge phase. This method of merging text indexes is similar to how [projections](/docs/reference/statements/alter/projection#projection-indexes) with `_part_offset` column are merged. If index is not materialized in the source part, it is built, written into a temporary file and then merged together with indexes from the other parts and from other temporary index files. **Debugging** Table function [mergeTreeTextIndex](/docs/reference/functions/table-functions/mergeTreeTextIndex) can be used to introspect text indexes.

Example: Hackernews dataset

Let's look at the performance improvements of text indexes on a large dataset with lots of text. We will use 28.7M rows of comments on the popular Hacker News website. Here is the table without text index: ```sql theme={null} CREATE TABLE hackernews ( id UInt64, deleted UInt8, type String, author String, timestamp DateTime, comment String, dead UInt8, parent UInt64, poll UInt64, children Array(UInt32), url String, score UInt32, title String, parts Array(UInt32), descendants UInt32 ) ENGINE = MergeTree ORDER BY (type, author); ``` The 28.7M rows are in a Parquet file in S3 - let's insert them into the `hackernews` table: ```sql theme={null} INSERT INTO hackernews SELECT * FROM s3Cluster( 'default', 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.parquet', 'Parquet', ' id UInt64, deleted UInt8, type String, by String, time DateTime, text String, dead UInt8, parent UInt64, poll UInt64, kids Array(UInt32), url String, score UInt32, title String, parts Array(UInt32), descendants UInt32'); ``` We will use `ALTER TABLE` and add a text index on comment column, then materialize it: ```sql theme={null} -- Add the index ALTER TABLE hackernews ADD INDEX comment_idx comment TYPE text(tokenizer = splitByNonAlpha); -- Materialize the index for existing data ALTER TABLE hackernews MATERIALIZE INDEX comment_idx SETTINGS mutations_sync = 2; ``` Now, let's run queries using `hasToken`, `hasAnyTokens`, and `hasAllTokens` functions. The following examples will show the dramatic performance difference between a standard index scan and the direct read optimization.

1. Using `hasToken`

`hasToken` checks if the text contains a specific single token. We'll search for the case-sensitive token 'ClickHouse'. **Direct read disabled (Standard scan)** By default, ClickHouse uses the skip index to filter granules and then reads the column data for those granules. We can simulate this behavior by disabling direct read. ```sql theme={null} SELECT count() FROM hackernews WHERE hasToken(comment, 'ClickHouse') SETTINGS query_plan_direct_read_from_text_index = 0; ┌─count()─┐ │ 516 │ └─────────┘ 1 row in set. Elapsed: 0.362 sec. Processed 24.90 million rows, 9.51 GB ``` **Direct read enabled (Fast index read)** Now we run the same query with direct read enabled (the default). ```sql theme={null} SELECT count() FROM hackernews WHERE hasToken(comment, 'ClickHouse') SETTINGS query_plan_direct_read_from_text_index = 1; ┌─count()─┐ │ 516 │ └─────────┘ 1 row in set. Elapsed: 0.008 sec. Processed 3.15 million rows, 3.15 MB ``` The direct read query is over 45 times faster (0.362s vs 0.008s) and processes significantly less data (9.51 GB vs 3.15 MB) by reading from the index alone.

2. Using `hasAnyTokens`

`hasAnyTokens` checks if the text contains at least one of the given tokens. We'll search for comments containing either 'love' or 'ClickHouse'. **Direct read disabled (Standard scan)** ```sql theme={null} SELECT count() FROM hackernews WHERE hasAnyTokens(comment, 'love ClickHouse') SETTINGS query_plan_direct_read_from_text_index = 0; ┌─count()─┐ │ 408426 │ └─────────┘ 1 row in set. Elapsed: 1.329 sec. Processed 28.74 million rows, 9.72 GB ``` **Direct read enabled (Fast index read)** ```sql theme={null} SELECT count() FROM hackernews WHERE hasAnyTokens(comment, 'love ClickHouse') SETTINGS query_plan_direct_read_from_text_index = 1; ┌─count()─┐ │ 408426 │ └─────────┘ 1 row in set. Elapsed: 0.015 sec. Processed 27.99 million rows, 27.99 MB ``` The speedup is even more dramatic for this common "OR" search. The query is nearly 89 times faster (1.329s vs 0.015s) by avoiding the full column scan.

3. Using `hasAllTokens`

`hasAllTokens` checks if the text contains all of the given tokens. We'll search for comments containing both 'love' and 'ClickHouse'. **Direct read disabled (Standard scan)** Even with direct read disabled, the standard skip index is still effective. It filters down the 28.7M rows to just 147.46K rows, but it still must read 57.03 MB from the column. ```sql theme={null} SELECT count() FROM hackernews WHERE hasAllTokens(comment, 'love ClickHouse') SETTINGS query_plan_direct_read_from_text_index = 0; ┌─count()─┐ │ 11 │ └─────────┘ 1 row in set. Elapsed: 0.184 sec. Processed 147.46 thousand rows, 57.03 MB ``` **Direct read enabled (Fast index read)** Direct read answers the query by operating on the index data, reading only 147.46 KB. ```sql theme={null} SELECT count() FROM hackernews WHERE hasAllTokens(comment, 'love ClickHouse') SETTINGS query_plan_direct_read_from_text_index = 1; ┌─count()─┐ │ 11 │ └─────────┘ 1 row in set. Elapsed: 0.007 sec. Processed 147.46 thousand rows, 147.46 KB ``` For this "AND" search, the direct read optimization is over 26 times faster (0.184s vs 0.007s) than the standard skip index scan. The direct read optimization also applies to compound boolean expressions. Here, we'll perform a case-insensitive search for 'ClickHouse' OR 'clickhouse'. **Direct read disabled (Standard scan)** ```sql theme={null} SELECT count() FROM hackernews WHERE hasToken(comment, 'ClickHouse') OR hasToken(comment, 'clickhouse') SETTINGS query_plan_direct_read_from_text_index = 0; ┌─count()─┐ │ 769 │ └─────────┘ 1 row in set. Elapsed: 0.450 sec. Processed 25.87 million rows, 9.58 GB ``` **Direct read enabled (Fast index read)** ```sql theme={null} SELECT count() FROM hackernews WHERE hasToken(comment, 'ClickHouse') OR hasToken(comment, 'clickhouse') SETTINGS query_plan_direct_read_from_text_index = 1; ┌─count()─┐ │ 769 │ └─────────┘ 1 row in set. Elapsed: 0.013 sec. Processed 25.87 million rows, 51.73 MB ``` By combining the results from the index, the direct read query is 34 times faster (0.450s vs 0.013s) and avoids reading the 9.58 GB of column data. For this specific case, `hasAnyTokens(comment, ['ClickHouse', 'clickhouse'])` would be the preferred, more efficient syntax.

Related content

* Blog: [Announcing General Availability of ClickHouse Full-text Search](https://clickhouse.com/blog/full-text-search-ga-release) * Blog: [Building high-performance full-text search for object storage](https://clickhouse.com/blog/clickhouse-full-text-search-object-storage) * Video: [Intro to Full-Text Search in ClickHouse](https://www.youtube.com/watch?v=9zPmf1a_heU) * Video: [Under the Hood: Full Text Search at ClickHouse scale and speed](https://www.youtube.com/watch?v=8JbqE_ubfkU) * Presentation: [Inside ClickHouse full-text search: fast, native and columnar](https://github.com/ClickHouse/clickhouse-presentations/blob/master/2025-tumuchdata-munich/ClickHouse_%20full-text%20search%20-%2011.11.2025%20Munich%20Database%20Meetup.pdf) * Presentation: [Inverted database indexes: The why, the what, and the how, FOSDEM 2026](https://presentations.clickhouse.com/2026-fosdem-inverted-index/Inverted_indexes_the_what_the_why_the_how.pdf) **Outdated material** * Blog: [Introducing Inverted Indices in ClickHouse](https://clickhouse.com/blog/clickhouse-search-with-inverted-indices) * Blog: [Inside ClickHouse full-text search: fast, native, and columnar](https://clickhouse.com/blog/clickhouse-full-text-search) * Video: [Full-Text Indices: Design and Experiments](https://www.youtube.com/watch?v=O_MnyUkrIq8)