Create Fulltext Index

MatrixOne supports full-text indexing, allowing users to perform efficient full-text searches on textual data in tables.

Syntax Description

MatrixOne supports full-text indexing, allowing users to perform efficient full-text searches on textual data in tables. Full-text indexes are suitable for columns containing char, varchar, text, json, and datalink data types, and are particularly optimized for text data in English and CJK (Chinese, Japanese, Korean) languages.

Syntax Structure

Enabling Full-Text Indexing

Full-text indexing is enabled by default. You can now create and use full-text indexes without any additional configuration.

Selecting the Boolean Mode Relevance Algorithm

Choose between BM25 or TF-IDF:

SET ft_relevancy_algorithm = BM25|TF-IDF; -- Default is TF-IDF for searches.

Creating a Full-Text Index

CREATE FULLTEXT INDEX <index_name> 
ON <table_name> (col1, col2, ...) 
[WITH PARSER (default | ngram | json | gojieba)];

  • index_name: The name you wish to assign to the full-text index.

  • table_name: The name of the table on which to create the index.

  • (col1, col2, ...): A list of columns to include in the full-text index.

  • WITH PARSER: Optional. Specifies the parser to use for indexing. Available options:

    • default: The default parser.

    • ngram: A parser supporting n-gram tokenization.

    • json: A parser specifically for JSON data.

    • gojieba: A parser for Chinese (CJK) text segmentation using the Jieba tokenizer. This parser performs word-level tokenization optimized for Chinese, Japanese, and Korean texts, providing better accuracy than ngram for CJK full-text searches. Supports all search modes including Boolean phrase matching.

WITH PARSER gojieba

The gojieba parser uses the Jieba Chinese text segmentation library to tokenize Chinese (CJK) text at the word level, rather than at the character or ngram level. This produces more semantically meaningful tokens for full-text search and relevance ranking.

The gojieba parser works with all full-text search modes:

  • Natural Language Mode: Performs a natural language search against jieba-tokenized words.

  • Boolean Mode: Supports boolean operators (+, -, *, ~, "") with jieba-segmented tokens. Chinese phrase queries in Boolean mode are tokenized through gojieba so they match the per-word rows the index stores.

You can create a full-text index using gojieba either as a standalone CREATE FULLTEXT INDEX statement or inline in the CREATE TABLE definition:

-- Standalone index creation
CREATE FULLTEXT INDEX ftidx ON src (body, title) WITH PARSER gojieba;

-- Inline in CREATE TABLE
CREATE TABLE src (
    id BIGINT PRIMARY KEY,
    body VARCHAR(200),
    title TEXT,
    FULLTEXT (title, body) WITH PARSER gojieba
);

Gojieba Chinese search example:

DROP DATABASE IF EXISTS gojieba_demo;
CREATE DATABASE gojieba_demo;
USE gojieba_demo;

CREATE TABLE src (
    id BIGINT PRIMARY KEY,
    body VARCHAR(200),
    FULLTEXT(body) WITH PARSER gojieba
);
INSERT INTO src VALUES
    (0, 'SGB11型号的检验报告在对素材文件进行搜索时'),
    (1, '使用全文索引会肥胖的原因都是因为摄入脂肪多导致的吗测试背景说明');

-- Natural language search
SELECT id FROM src WHERE MATCH(body) AGAINST('肥胖的原因都是因为摄入脂肪多导致的吗' IN NATURAL LANGUAGE MODE);

-- Boolean search with Chinese phrases
SELECT id FROM src WHERE MATCH(body) AGAINST('+SGB11型号的检验报告' IN BOOLEAN MODE);

DROP DATABASE gojieba_demo;

Performing Full-Text Searches

MATCH (col1, col2, ...) AGAINST (expr [search_modifier]);

  • (col1, col2, ...): The columns to search.

  • expr: The search expression or keywords.

  • search_modifier: Optional. Specifies the search mode. Available options:

    • IN NATURAL LANGUAGE MODE: Performs a natural language search.

    • IN BOOLEAN MODE: Performs a Boolean search, allowing operators like +, -, and *.

Usage with Joins

The fulltext index rewrite can apply to INNER JOIN queries. When a fulltext index exists on a table that participates in an INNER JOIN and the MATCH() AGAINST() condition references columns from that table’s index, the optimizer can rewrite the query to use the fulltext index scan.

  • The fulltext table can be on either side of the INNER JOIN.

  • Multiple fulltext filters on the same scan, and fulltext filters on both JOIN children, are supported.

  • Nested INNER JOINs are supported.

  • Cross-table MATCH() conditions (where columns from different tables are mixed in a single MATCH) are not rewritten.

  • Outer joins (LEFT JOIN, RIGHT JOIN) are not rewritten in the current phase. Using MATCH() AGAINST() in outer join queries will not leverage the fulltext index.

DROP DATABASE IF EXISTS ft_join_demo;
CREATE DATABASE ft_join_demo;
USE ft_join_demo;

CREATE TABLE articles (
    id VARCHAR(191) PRIMARY KEY,
    base_id VARCHAR(191),
    title VARCHAR(512),
    body LONGTEXT,
    FULLTEXT INDEX ft_idx(title, body)
);

CREATE TABLE categories (
    id VARCHAR(191) PRIMARY KEY,
    name VARCHAR(191),
    note TEXT,
    FULLTEXT INDEX cat_ft_idx(name, note)
);

INSERT INTO articles VALUES
    ('a1', 'b1', 'hello title', 'hello body'),
    ('a2', 'b2', 'other title', 'other body');

INSERT INTO categories VALUES
    ('b1', 'Category One', 'category hello note'),
    ('b2', 'Category Two', 'category other note');

-- INNER JOIN with fulltext filter on the left table
SELECT a.id, c.name
FROM articles a JOIN categories c ON c.id = a.base_id
WHERE MATCH(a.title, a.body) AGAINST('hello')
ORDER BY a.id;

-- INNER JOIN with fulltext filter on the right table
SELECT a.id, c.name
FROM categories c JOIN articles a ON c.id = a.base_id
WHERE MATCH(a.title, a.body) AGAINST('hello')
ORDER BY a.id;

-- Fulltext filters on both JOIN sides
SELECT a.id, c.name
FROM articles a JOIN categories c ON c.id = a.base_id
WHERE MATCH(a.title, a.body) AGAINST('hello')
  AND MATCH(c.name, c.note) AGAINST('category')
ORDER BY a.id;

DROP DATABASE ft_join_demo;

Membership Pushdown Filters

Full-text search supports membership pushdown filters that can pre-filter rows using relational predicates before performing full-text matching. This optimization applies additional WHERE clause conditions to build a candidate document ID set, which then filters the full-text index scan.

The pushdown uses different internal filter structures depending on the source table’s primary key type:

  • Integer PK, small ID range: A dense cbitmap for efficient filtering.

  • Integer PK, wide ID span (> 2^23): A compact CRoaring bitset for sparse but large-range IDs.

  • Varchar (non-integer) PK: A CBloomFilter, which is probabilistic and may admit a small number of false positives.

To enable Bloom filter-based pushdown for varchar primary key tables, set the system variable fulltext_bloom_filter_pushdown to 1 (default is 0):

SET fulltext_bloom_filter_pushdown = 1;

The pushdown is transparent to query results. The predicate produces the candidate document ID set that builds the filter, and the full-text match then operates only on those candidate rows.

Example with membership pushdown:

DROP DATABASE IF EXISTS ft_membership_demo;
CREATE DATABASE ft_membership_demo;
USE ft_membership_demo;

CREATE TABLE docs (
    id BIGINT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    body TEXT NOT NULL,
    category VARCHAR(50) NOT NULL
);
INSERT INTO docs VALUES
    (1, 'Introduction to Machine Learning', 'machine learning is a subset of artificial intelligence', 'tech'),
    (2, 'Deep Learning Fundamentals',       'deep learning uses neural networks to learn from data', 'tech'),
    (3, 'Cooking with Machine Learning',    'using machine learning to recommend recipes', 'food'),
    (4, 'The Art of French Cooking',        'french cooking techniques from around the world', 'food');

CREATE FULLTEXT INDEX ftidx ON docs (title, body);

-- Category predicate pre-filters rows before fulltext matching
SELECT id, title, category FROM docs
WHERE MATCH(title, body) AGAINST('machine learning') AND category = 'tech'
ORDER BY id;

DROP DATABASE ft_membership_demo;

Examples

-- Replace <your_table_name> and <your_index_name> with actual identifiers
CREATE TABLE example_table (
    id INT PRIMARY KEY,
    english_text TEXT,       -- English text
    chinese_text TEXT,       -- Chinese text
    json_data JSON           -- JSON data
);
INSERT INTO example_table (id, english_text, chinese_text, json_data) VALUES
(1, 'Hello, world!', '你好世界', '{"name": "Alice", "age": 30}'),
(2, 'This is a test.', '这是一个测试', '{"name": "Bob", "age": 25}'),
(3, 'Full-text search is powerful.', '全文搜索很强大', '{"name": "Charlie", "age": 35}');

-- Create a full-text index using the default parser
mysql> CREATE FULLTEXT INDEX idx_english_text ON example_table (english_text);
Query OK, 0 rows affected (0.03 sec)

-- Create a full-text index using the ngram parser
mysql> CREATE FULLTEXT INDEX idx_chinese_text ON example_table (chinese_text) WITH PARSER ngram;
Query OK, 0 rows affected (0.02 sec)

-- Create a full-text index using the json parser
mysql> CREATE FULLTEXT INDEX idx_json_data ON example_table (json_data) WITH PARSER json;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE example_table;
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table         | Create Table                                                                                                                                                                                                                                                                                                                                               |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| example_table | CREATE TABLE `example_table` (
  `id` int NOT NULL,
  `english_text` text DEFAULT NULL,
  `chinese_text` text DEFAULT NULL,
  `json_data` json DEFAULT NULL,
  PRIMARY KEY (`id`),
 FULLTEXT `idx_english_text`(`english_text`),
 FULLTEXT `idx_chinese_text`(`chinese_text`) WITH PARSER ngram,
 FULLTEXT `idx_json_data`(`json_data`) WITH PARSER json
) |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- Search for English text containing "world"
mysql> SELECT * FROM example_table WHERE MATCH(english_text) AGAINST('world');
+------+---------------+--------------+------------------------------+
| id   | english_text  | chinese_text | json_data                    |
+------+---------------+--------------+------------------------------+
|    1 | Hello, world! | 你好世界     | {"age": 30, "name": "Alice"} |
+------+---------------+--------------+------------------------------+
1 row in set (0.02 sec)

-- Search for Chinese text containing "你好"
mysql> SELECT * FROM example_table WHERE MATCH(chinese_text) AGAINST('你好');
+------+---------------+--------------+------------------------------+
| id   | english_text  | chinese_text | json_data                    |
+------+---------------+--------------+------------------------------+
|    1 | Hello, world! | 你好世界     | {"age": 30, "name": "Alice"} |
+------+---------------+--------------+------------------------------+
1 row in set (0.01 sec)

-- Search for JSON data containing "Alice"
mysql> SELECT * FROM example_table WHERE MATCH(json_data) AGAINST('Alice');
+------+---------------+--------------+------------------------------+
| id   | english_text  | chinese_text | json_data                    |
+------+---------------+--------------+------------------------------+
|    1 | Hello, world! | 你好世界     | {"age": 30, "name": "Alice"} |
+------+---------------+--------------+------------------------------+
1 row in set (0.01 sec)

-- Using Boolean mode for searches

mysql> SET ft_relevancy_algorithm = "BM25";
Query OK, 0 rows affected (0.00 sec)

-- 1. Using the "+" operator: Must include "test"
mysql> SELECT * FROM example_table WHERE MATCH(english_text) AGAINST('+test' IN BOOLEAN MODE);
+------+-----------------+--------------------+----------------------------+
| id   | english_text    | chinese_text       | json_data                  |
+------+-----------------+--------------------+----------------------------+
|    2 | This is a test. | 这是一个测试       | {"age": 25, "name": "Bob"} |
+------+-----------------+--------------------+----------------------------+
1 row in set (0.01 sec)

-- 2. Using the "-" operator: Must exclude "This"
mysql> SELECT * FROM example_table WHERE MATCH(english_text) AGAINST('+test -This' IN BOOLEAN MODE);
Empty set (0.00 sec)

-- 3. Using the "*" operator: Matches words starting with "pow"
mysql> SELECT * FROM example_table WHERE MATCH(english_text) AGAINST('pow*' IN BOOLEAN MODE);
+------+-------------------------------+-----------------------+--------------------------------+
| id   | english_text                  | chinese_text          | json_data                      |
+------+-------------------------------+-----------------------+--------------------------------+
|    3 | Full-text search is powerful. | 全文搜索很强大        | {"age": 35, "name": "Charlie"} |
+------+-------------------------------+-----------------------+--------------------------------+
1 row in set (0.01 sec)

-- 4. Using double quotes "" to match the exact phrase "search is powerful"
mysql> SELECT * FROM example_table WHERE MATCH(english_text) AGAINST('"search is powerful"' IN BOOLEAN MODE);
+------+-------------------------------+-----------------------+--------------------------------+
| id   | english_text                  | chinese_text          | json_data                      |
+------+-------------------------------+-----------------------+--------------------------------+
|    3 | Full-text search is powerful. | 全文搜索很强大        | {"age": 35, "name": "Charlie"} |
+------+-------------------------------+-----------------------+--------------------------------+
1 row in set (0.02 sec)