Full-Text Search Lab with ClickHouse — Bilingual (ko/en) Support Ticket Search

A hands-on laboratory that builds a bilingual (Korean + English) full-text search over 1,000,000 customer-support tickets and rigorously compares the three indexing strategies ClickHouse offers — tokenbf_v1, ngrambf_v1, and the GA text inverted index — side by side.

The lab is designed to be driven from Claude Code through the mcp-clickhouse MCP server against ClickHouse Cloud, so each SQL block can be executed and reasoned about interactively.

Why this lab exists

Most full-text search demos use English-only Wikipedia dumps and the GA text index "just works". Korean is a different story: there is no built-in morphological analyzer, particles (조사) attach to nouns, spacing is inconsistent, and customers freely mix English error codes into Korean prose. The lab quantifies exactly where the index helps, where it silently misses matches, and what to do about it.

What you will measure

Dataset

• 1,000,000 tickets, partitioned by month over the last 180 days
• ~2,000,000 ticket messages (multi-turn conversations)
• 10 knowledge-base articles (FAQ matching target)
• Language mix: 70% Korean, 20% mixed ko/en, 10% English
• Realistic content: actual Korean support-ticket phrasings (조사 변형 포함), product names, error codes
• 4 identical copies of the tickets table with different index strategies, so every comparison is apples-to-apples

File structure

fulltext-search/
├── README.md                   # this file
├── 01-schema.sql               # 4 ticket tables × 3 index strategies + articles + messages
├── 02-load.sql                 # generate 1M bilingual tickets + 2M messages
├── 03-baseline-search.sql      # LIKE / position / multiSearchAny / hasToken (no index)
├── 04-index-comparison.sql     # tokenbf vs ngrambf vs text(asciiCJK) vs text(ngrams)
├── 05-korean-limits.sql        # 조사 / 띄어쓰기 / 어간 / 복합명사 / 혼용 한계
├── 06-real-scenarios.sql       # live search, trending detection, similar tickets, MTTR, MV
└── 07-management.sql           # drop/add/materialize indexes, monitoring, cleanup

Prerequisites

• ClickHouse 26.2+ (text index GA). Earlier 25.x clusters work but the text(...) index falls back to scan; the comparison still produces useful numbers.
• A ClickHouse Cloud service (any size — even Development tier handles 1M rows fine).
• Local Python 3.10+ if you want to drive the lab via MCP from Claude Code.

Connect Claude Code → ClickHouse Cloud via MCP

1. Grab your service connection details

In ClickHouse Cloud → your service → Connect → HTTPS. Copy: - host (e.g. abc123.us-east-1.aws.clickhouse.cloud) - username (default: default) - the password you set when creating the service

2. Add the MCP server

For Claude Code CLI:

claude mcp add clickhouse-cloud \
  --scope project \
  --env CLICKHOUSE_HOST=YOUR_HOST.clickhouse.cloud \
  --env CLICKHOUSE_USER=default \
  --env CLICKHOUSE_PASSWORD='YOUR_PASSWORD' \
  --env CLICKHOUSE_SECURE=true \
  --env CLICKHOUSE_PORT=8443 \
  --env CLICKHOUSE_DATABASE=support_search \
  -- uv run --with mcp-clickhouse --python 3.10 mcp-clickhouse

For Claude Desktop (~/Library/Application Support/Claude/claude_desktop_config.json on macOS):

{
  "mcpServers": {
    "clickhouse-cloud": {
      "command": "uv",
      "args": ["run", "--with", "mcp-clickhouse", "--python", "3.10", "mcp-clickhouse"],
      "env": {
        "CLICKHOUSE_HOST": "YOUR_HOST.clickhouse.cloud",
        "CLICKHOUSE_USER": "default",
        "CLICKHOUSE_PASSWORD": "YOUR_PASSWORD",
        "CLICKHOUSE_SECURE": "true",
        "CLICKHOUSE_PORT": "8443",
        "CLICKHOUSE_DATABASE": "support_search",
        "CLICKHOUSE_CONNECT_TIMEOUT": "30",
        "CLICKHOUSE_SEND_RECEIVE_TIMEOUT": "60"
      }
    }
  }
}

Restart Claude Code / Claude Desktop. You should now have mcp__clickhouse-cloud__run_select_query and friends available as tools.

3. Verify

In a Claude Code session ask: "connect to clickhouse-cloud and run SELECT version()" — expect ClickHouse 26.x.

Running the lab

Once MCP is wired up you can ask Claude Code to execute each script in turn — it will call run_select_query / run_command for every statement and stream the output back. Or run from your shell:

cd usecase/fulltext-search

# Replace with your Cloud creds, or rely on a ~/.clickhouse-client/config.xml
export CH_OPTS="--host YOUR_HOST.clickhouse.cloud --port 9440 --secure \
                --user default --password 'YOUR_PASSWORD'"

for f in 01-schema.sql 02-load.sql 03-baseline-search.sql \
         04-index-comparison.sql 05-korean-limits.sql \
         06-real-scenarios.sql 07-management.sql; do
    echo "▶ $f"
    clickhouse-client $CH_OPTS --queries-file "$f"
done

Estimated total runtime on a ClickHouse Cloud development service:

What you will conclude (preview of the punchline)

After running the lab, the expected findings:

1. English exact-token queries (PAYMENT_DECLINED): tokenbf_v1 and text(asciiCJK) are roughly tied at ~10× faster than full scan. ngrambf_v1 does fine but isn't required.
2. Korean 2-char queries (결제): tokenbf_v1 returns near-zero hits because particles fragment the token space. text(asciiCJK) returns correct hits with full index pruning. ngrambf_v1+LIKE is correct but slower.
3. Korean phrase queries (결제 실패): hasPhrase() on text(asciiCJK) is both fastest and most accurate. Bloom filters can only fall back to LIKE which means full scans behind the index probe.
4. Particle/spacing variants (로그인이 안 됩니다 vs 로그인안됨): no tokenizer recovers all of these on its own — you must normalize the query side-side (OR-expand the variants) regardless of which index you choose.
5. On-disk cost: text(ngrams=2) is by far the largest (often 30–60% of base table); text(asciiCJK) is moderate (10–20%); tokenbf_v1 is smallest (~5%). Pick asciiCJK as the default and only add ngrams if short-substring search drives revenue.

Author

Ken (ClickHouse Solution Architect) · 2026-06-07