CREATE FULLTEXT2 INDEX¶

Create a FULLTEXT2 index on text columns so rows can be searched with MATCH ... AGAINST. FULLTEXT2 is a WAND positional engine whose natural-language mode is an exact phrase match, and it is gated behind the experimental_fulltext2_index session variable (default off).

Description¶

CREATE FULLTEXT2 INDEX builds a full-text index over one or more text columns. Queries use MATCH(cols) AGAINST ('query' [mode]) to retrieve matching rows. FULLTEXT2’s natural-language mode is an exact, contiguous phrase match, unlike classic full-text’s bag-of-words natural language; boolean mode supports the +/-/~/</>/(/)/*/"..." operators, and IN BM25 MODE performs BM25-ranked retrieval.

The index is gated behind experimental_fulltext2_index (default off), which must be enabled before creating or querying it. A standalone CREATE FULLTEXT2 INDEX builds the base index synchronously from existing rows; post-create DML flows into the index asynchronously through CDC.

Syntax¶

CREATE FULLTEXT2 INDEX [index_name] ON table_name (index_column_list)
    [index_option_list];

-- or inline, on a column definition:
CREATE TABLE t (
    id BIGINT PRIMARY KEY,
    body TEXT,
    FULLTEXT2 [index_name] (body)
);

Arguments¶

Option	Description
`PARSER ngram`	Default parser: CJK sliding 3-gram, Latin whole-word tokenization.
`PARSER gojieba`	Dictionary word segmentation for Chinese text.
`PARSER json`	Flatten JSON values and index them with ngram tokenization.
`PARSER json_value`	Index each JSON leaf value as one whole atomic token, matched exactly.
`MAX_INDEX_CAPACITY n`	Maximum documents per segment; a segment seals when this or `MAX_POSTINGS_CAPACITY` is reached.
`MAX_POSTINGS_CAPACITY n`	Maximum term occurrences per segment; bounds per-segment build memory.
`POSITION_FREE = TRUE	FALSE`
`AUTO_UPDATE = TRUE	FALSE`
`DAY n`, `HOUR n`, `SECOND n`	Auto-update compaction cadence.
`INCLUDE (cols)`	Store the typed value of chosen scalar columns in the index so a `WHERE` predicate on them is evaluated in-index and a `SELECT` of them is served from the index.
`MERGE`	(Reindex) fold the base sub-segments.
`FORCE_SYNC`	(Reindex) run the merge or rebuild inline during `ALTER`, instead of in the background.

Search Modes¶

Mode	Description
`IN NATURAL LANGUAGE MODE` (default)	Exact, contiguous phrase match.
`IN BOOLEAN MODE`	`+`/`-`/`~`/`<`/`>`/`(`/`)`/`*`/`"..."` operators.
`IN BM25 MODE`	BM25-ranked bag-of-words retrieval.

Examples¶

DROP DATABASE IF EXISTS fulltext2_demo;
CREATE DATABASE fulltext2_demo;
USE fulltext2_demo;

SET experimental_fulltext2_index = 1;

CREATE TABLE docs (id BIGINT PRIMARY KEY, body TEXT);
INSERT INTO docs VALUES
 (0, 'the quick brown fox jumps'),
 (1, 'a quick brown dog'),
 (2, 'the lazy fox sleeps'),
 (3, 'brown bear and lazy cat'),
 (4, 'quick quick quick fox fox');

CREATE FULLTEXT2 INDEX ft ON docs(body);

-- exact phrase in natural-language mode
SELECT id FROM docs WHERE MATCH(body) AGAINST('quick brown fox') ORDER BY id;

-- boolean mode
SELECT id FROM docs WHERE MATCH(body) AGAINST('+quick +fox' IN BOOLEAN MODE) ORDER BY id;

-- BM25 mode
SELECT id FROM docs WHERE MATCH(body) AGAINST('lazy' IN BM25 MODE) ORDER BY id;

DROP DATABASE fulltext2_demo;