fts5-cjk

The problem

FTS5 ships three tokenizers and none of them work for CJK text:

• unicode61 (the default) has no idea where CJK words end, so a whole sentence becomes one token. MATCH '魚池' finds nothing inside 南投縣魚池鄉.
• trigram needs three characters per token. Most Chinese words are two characters, so everyday queries return nothing.
• porter is for English.

What cjk does

CJK runs are indexed as overlapping character bigrams, the same idea as Lucene's CJKAnalyzer and Elasticsearch's cjk analyzer. Text in any other script is passed to unicode61 untouched, so Latin words keep case folding, diacritic handling and all of unicode61's options.

.load ./cjk
CREATE VIRTUAL TABLE t USING fts5(body, tokenize='cjk');
INSERT INTO t VALUES ('日月潭紅茶產於南投縣魚池鄉');

SELECT * FROM t WHERE t MATCH '魚池';       -- found: substring match
SELECT * FROM t WHERE t MATCH '南投 紅茶';   -- found: two words, ANDed
SELECT * FROM t WHERE t MATCH '南投魚池';    -- not found: one bareword is one phrase,
                                            -- so 南投 and 魚池 would have to be adjacent
SELECT highlight(t, 0, '[', ']') FROM t WHERE t MATCH '魚池鄉';
-- 日月潭紅茶產於南投縣[魚池鄉]

Arguments after the name go to unicode61: tokenize='cjk remove_diacritics 2'

Build

make        # cjk.dylib on macOS, cjk.so elsewhere
make test   # runs test.sql through sqlite3 and diffs against test.expected

You need sqlite3ext.h from an SQLite built with FTS5. On macOS use Homebrew's (brew install sqlite); the system sqlite3 refuses to load extensions. On Debian and Ubuntu install libsqlite3-dev.

Results

Chinese

Same 47,281 articles from Taiwan's national law database, three tables that differ only in tokenize=. Numbers are how many articles the query returns. The cjk column is identical to a full-table LIKE '%勞工%' scan, i.e. every article that actually contains the word; the other two columns show how many of those the built-in tokenizers find.

unicode61 only hits when punctuation happens to isolate the word. trigram is fine for four-character terms and blind to two-character ones. The 臺灣 row also counts 台灣, which cjk folds to the same form. Reproduce with bench/law.sh: it downloads the data, rebuilds the tables and fails if any cjk count differs from the LIKE scan.

Japanese

4,011 paragraphs from 40 Japanese Wikipedia articles.

Japanese has no spaces, so unicode61 sees whole clauses as one token and two-character words vanish. Three-character words and longer are where trigram catches up.

Korean

2,095 paragraphs from 40 Korean Wikipedia articles.

Korean is written with spaces, so unicode61 does find the bare word. It misses every form with a particle attached (서울은, 서울의, 서울에서), which is most of them. Two-syllable words are again invisible to trigram.

Things to know

• One bareword is one phrase. MATCH '魚池鄉' means the substring 魚池鄉. To AND two words, separate them with a space.
• By default, a single-character query only matches documents where that character stands alone. Use MATCH '茶*', or create the table with tokenize='cjk unigram 1' to index single characters too (about 70% larger index on 47k law articles).
• Full-width ASCII and half-width katakana (including voiced marks) are folded. The variants 臺→台, 裡→裏, 羣→群, 峯→峰, 綫→線, 衞→衛, 爲→為, 眞→真, 敎→教, 册→冊, 祕→秘, 啓→啟, 淸→清, 靑→青 are folded at index and query time; other traditional/simplified mappings are not.
• Rebuild existing indexes after upgrading: INSERT INTO t(t) VALUES('rebuild').
• Han, hiragana, katakana and hangul count as CJK. CJK punctuation is a separator.
• Without unigram 1, index size is in the same range as trigram.

Languages

Alternatives

Why bigrams instead of a dictionary

A dictionary tokenizer is only as good as its dictionary, and Traditional Chinese dictionaries are thin. Bigrams never miss a word they have not seen. What they cannot do is split a no-space query such as 南投魚池 into two words. FTS5 decides phrase structure before it calls the tokenizer, so that split has to happen above FTS5, for example in an SQL function that rewrites the query. That is the planned next step.

FAQ

Why doesn't SQLite FTS5 work for Chinese full-text search?

The built-in unicode61 tokenizer does not know where CJK words end, so a whole sentence becomes one token. trigram needs three characters per token, so two-character words return nothing. porter is for English.

How do I do Chinese, Japanese and Korean full-text search in SQLite FTS5?

Load fts5-cjk and create the table with tokenize='cjk'. Runs of CJK text are indexed as overlapping character bigrams; text in any other script goes to unicode61.

How does fts5-cjk compare with the built-in tokenizers?

On 47,281 articles from Taiwan's national law database, a search for 勞工 returns 11 articles with unicode61, 0 with trigram and 711 with cjk, which matches a full-table LIKE scan.

Does fts5-cjk need a dictionary or dependencies?

No. It is one C file with no dictionary and no dependencies, under the MIT license.

License

MIT. Source: github.com/Ray0907/fts5-cjk.