Full-text search with text indexes

**Debugging**

Table function [mergeTreeTextIndex](/docs/sql-reference/table-functions/mergeTreeTextIndex) can be used to introspect text indexes.

## Example: Hackernews dataset[​](#hacker-news-dataset "Direct link to 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:

```
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:

```
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:

```
-- 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`[​](#using-hasToken "Direct link to 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.

```
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).

```
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`[​](#using-hasAnyTokens "Direct link to 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)**

```
SELECT count()
FROM hackernews
WHERE hasAnyTokens(comment, 'love ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 0, use_skip_indexes_on_data_read = 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)**

```
SELECT count()
FROM hackernews
WHERE hasAnyTokens(comment, 'love ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 1, use_skip_indexes_on_data_read = 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`[​](#using-hasAllTokens "Direct link to 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.

```
SELECT count()
FROM hackernews
WHERE hasAllTokens(comment, 'love ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 0, use_skip_indexes_on_data_read = 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.

```
SELECT count()
FROM hackernews
WHERE hasAllTokens(comment, 'love ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 1, use_skip_indexes_on_data_read = 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.

### 4. Compound search: OR, AND, NOT, ...[​](#compound-search "Direct link to 4. Compound search: OR, AND, NOT, ...")

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)**

```
SELECT count()
FROM hackernews
WHERE hasToken(comment, 'ClickHouse') OR hasToken(comment, 'clickhouse')
SETTINGS query_plan_direct_read_from_text_index = 0, use_skip_indexes_on_data_read = 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)**

```
SELECT count()
FROM hackernews
WHERE hasToken(comment, 'ClickHouse') OR hasToken(comment, 'clickhouse')
SETTINGS query_plan_direct_read_from_text_index = 1, use_skip_indexes_on_data_read = 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[​](#related-content "Direct link to Related content")

- Presentation: <https://github.com/ClickHouse/clickhouse-presentations/blob/master/2025-tumuchdata-munich/ClickHouse_%20full-text%20search%20-%2011.11.2025%20Munich%20Database%20Meetup.pdf>
- Presentation: <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)