Skip to main content

Full-Text Search

Finds log, event, and alarm bodies using partial string matching. Works without ALLOW FILTERING, and also works with Korean.

SELECT ts, msg FROM logs
 WHERE device = 'pump-01'
   AND ts >= '2026-07-31 00:00' AND ts < '2026-07-31 01:00'
   AND msg LIKE '%타임아웃%';

Combines directly with time-series query patterns — narrows down the partition and time range, then filters by body content.

Creating an Index​

CREATE TABLE logs (
    device text, ts timestamp, msg text,
    PRIMARY KEY (device, ts)
) WITH CLUSTERING ORDER BY (ts ASC);

CREATE INDEX logs_msg_idx ON logs(msg) USING 'sai'
  WITH OPTIONS = { 'index_analyzer': 'ngram' };

index_analyzer accepts a preset name or a JSON definition.

PresetTokenizationLIKE?Use Case
ngramCharacter n-grams (2–3) over the entire value, including spaces, case preservedYesPartial string search in logs and messages
standardUnicode words, lowercasedNoWord-level matching
cjkWords + CJK bigrams, lowercasedNoAstra-compatible CJK matching
keywordEntire value as-isNoAnalyzer only, no tokenization

If partial string search is the goal, ngram is the choice — that's why this feature exists.

-- 대소문자를 무시하려면 lowercase 필터를 더한다
CREATE INDEX logs_msg_idx ON logs(msg) USING 'sai'
  WITH OPTIONS = { 'index_analyzer': '{
      "tokenizer": {"name": "ngram", "args": {"minGramSize": "2", "maxGramSize": "3"}},
      "filters": [{"name": "lowercase"}]
  }' };

If you include lowercase, LIKE ignores case; if you omit it, case is respected (standard SQL semantics).

Querying​

-- 부분 문자열 — 단어 중간도 잡힙니다
... AND msg LIKE '%timeout%';

-- 접두 · 접미 · 완전 일치
... AND msg LIKE 'connection%';
... AND msg LIKE '%port 9042';
... AND msg LIKE 'connection refused';

-- 조각 여러 개를 AND — 조건을 반복하면 됩니다
... AND msg LIKE '%connection%' AND msg LIKE '%refused%';

-- '=' 는 분석기를 거치되 «값 전체» 일치를 유지합니다
... AND msg = 'connection timeout on port 9042';

Can be mixed with time-series functions.

SELECT time_bucket(5m, ts) AS bucket, count(*) AS errors
FROM logs
WHERE device = 'pump-01' AND ts >= ? AND ts < ? AND msg LIKE '%timeout%'
GROUP BY device, time_bucket(5m, ts);

Why It's Accurate​

The index provides recall; re-checking the original value provides precision.

Since n-grams are indexed by sliding across the entire value (including spaces and punctuation), every gram of any substring is guaranteed to be indexed — no false negatives. False positives, where grams are scattered and happen to match, are filtered out at the end by re-applying the original pattern to the original value.

This is why Korean (and CJK, using the same n-gram approach), fragments spanning spaces ('%프 정%'), and mid-word occurrences ('%imeou%' → "timeout") are all captured.

Limitations​

  • index_analyzer cannot be used together with the old case_sensitive, normalize, and ascii options.
  • Cannot be applied to primary key columns or collection columns.
  • 🔴 ALTER INDEX is not available. To change the analyzer, you must DROP INDEX then CREATE INDEX — a full rebuild. Since this takes time during operation, decide upfront.

See Also​