PolarDB:IMCI full-text index

Version requirements

Prerequisites

Before you begin, ensure that you have:

• A PolarDB for MySQL cluster that meets the version requirements above

• A table with the column store enabled (COMMENT 'columnar=1')

• The imci_enable_fts_query system variable set to ON for the session where you run queries

Choose a tokenizer

Select a tokenizer based on your data type and search pattern. Each tokenizer splits text differently and suits different query scenarios.

Not sure which to use? Preview how each tokenizer processes your text before creating the index:

CALL dbms_imci.fts_tokenize("I am PolarDB");
-- Result: ["i", "am", "polardb"]  (default: token, type=0)

CALL dbms_imci.fts_tokenize("I am PolarDB", "type=1");
-- Result: ["i ", " a", "am", "m ", " p", "po", "ol", "ar", "rd", "db"]  (ngram)

CALL dbms_imci.fts_tokenize("I am PolarDB", "type=2");
-- Result: ["PolarDB"]  (jieba, accurate mode)

CALL dbms_imci.fts_tokenize("I am PolarDB", "type=2,mode=1");
-- Result: ["polardb"]  (jieba, full mode)

Create a full-text index

Set the table-level COMMENT to columnar=1 and the column-level COMMENT to imci_fts(type=VALUE) to define an inverted index on that column.

Create the index at table creation time:

CREATE TABLE t1 (
    id    INT PRIMARY KEY,
    title VARCHAR(32) COMMENT "imci_fts(type=2)"
) CHARSET utf8mb4 COMMENT 'columnar=1';

Add or modify an index on an existing column:

ALTER TABLE t1 MODIFY title VARCHAR(32) COMMENT "imci_fts(type=2,mode=0)";

After the ALTER completes, IMCI builds the index in the background. Run queries only after the build finishes — check progress using the monitoring commands in Monitor index build status.

Remove an index:

ALTER TABLE t1 MODIFY title VARCHAR(255) COMMENT 'imci_fts(enable=0)';

Run full-text queries

Enable full-text query support for the session before running queries:

SET imci_enable_fts_query = ON;

-- Insert sample data
INSERT INTO t1 VALUES
    (16, 'polarDB full-text index feature title'),
    (17, 'database title performance optimization');

-- Find rows where the title column contains "title"
SELECT * FROM t1 WHERE MATCH(title) AGAINST("title");

EXPLAIN SELECT * FROM t1 WHERE MATCH(title) AGAINST("title") AND id > 10;

+----+------------------------+------+-----------------------------------------------------------------+
| ID | Operator               | Name | Extra Info                                                      |
+----+------------------------+------+-----------------------------------------------------------------+
|  1 | Select Statement       |      | IMCI Execution Plan (max_dop = 32, max_query_mem = 41230008320) |
|  2 | └─Compute Scalar       |      |                                                                 |
|  3 |   └─FILTER             |      | Cond: (t1.id > 10)                                              |
|  4 |     └─FtsTableScan     | t1   | Term: ("title") Fallback: (t1.title LIKE "%title%")             |
+----+------------------------+------+-----------------------------------------------------------------+

SET imci_convert_like_to_match = ON;

EXPLAIN SELECT * FROM t1 WHERE title LIKE "%title%";

+----+------------------------+------+-----------------------------------------------------------------+
| ID | Operator               | Name | Extra Info                                                      |
+----+------------------------+------+-----------------------------------------------------------------+
|  1 | Select Statement       |      | IMCI Execution Plan (max_dop = 32, max_query_mem = 41230008320) |
|  2 | └─Compute Scalar       |      |                                                                 |
|  3 |   └─FILTER             |      | Cond: (t1.title LIKE "%title%")                                 |
|  4 |     └─FtsTableScan     | t1   | Term: ("title") Fallback: (t1.title LIKE "%title%")             |
+----+------------------------+------+-----------------------------------------------------------------+

SET imci_enable_query_fts_like = ON;

EXPLAIN SELECT * FROM t1 WHERE MATCH(title) AGAINST("title");

+----+----------------------+------+-----------------------------------------------------------------+
| ID | Operator             | Name | Extra Info                                                      |
+----+----------------------+------+-----------------------------------------------------------------+
|  1 | Select Statement     |      | IMCI Execution Plan (max_dop = 32, max_query_mem = 41230008320) |
|  2 | └─Compute Scalar     |      |                                                                 |
|  3 |   └─Table Scan       | t1   | Cond: (title LIKE "%title%")                                    |
+----+----------------------+------+-----------------------------------------------------------------+

Monitor index build status

After creating or modifying a full-text index, track the build progress using these commands:

-- List all inverted indexes
SHOW imci indexes fulltext;
SELECT * FROM information_schema.imci_fts_indexes;

-- Filter to a specific table
SHOW imci indexes fulltext FOR [db_name].[table_name];
SELECT * FROM information_schema.imci_fts_indexes
    WHERE schema_name = '[db_name]' AND table_name = '[table_name]';

-- View index metadata
SELECT * FROM information_schema.imci_fts_index_metas
    WHERE schema_name = '[db_name]' AND table_name = '[table_name]' AND column_name = '[column_name]';

-- View segment data
SELECT * FROM information_schema.imci_fts_index_segs
    WHERE schema_name = '[db_name]' AND table_name = '[table_name]' AND column_name = '[column_name]';

-- View column store data
SELECT * FROM information_schema.imci_fts_index_packs
    WHERE schema_name = '[db_name]' AND table_name = '[table_name]' AND column_name = '[column_name]';

System variables

These system variables control global index build behavior and query conversion. Set global variables in the PolarDB console — they cannot be changed from the command line. Session-scoped variables can be set per connection.

FAQ

My query isn't using the full-text index. What's wrong?

The most common cause is that imci_enable_fts_query is still set to OFF. Run SET imci_enable_fts_query = ON; and try again.

If the index is still not used, check the EXPLAIN output. If FtsTableScan is absent, the query pattern may not match the index, or the query optimizer chose a full table scan due to lower estimated cost.

Which tokenizer should I use?

Start with the default type=0 (token) for English or structured text. For Chinese content, use type=2 (jieba) in accurate mode for semantic search, or type=3 (IK Analyzer) as an alternative. For JSON columns, use type=4 (json) with the expr parameter to specify the JSONPath to index.

To confirm your choice before creating the index, preview tokenization results with dbms_imci.fts_tokenize. See Choose a tokenizer.

What is the difference between `LIKE` and `MATCH...AGAINST`?

LIKE '%keyword%' without a full-text index causes a full table scan. MATCH...AGAINST uses the inverted index for fast lookups and also supports Boolean queries and relevance scoring. For new applications, use MATCH...AGAINST directly. For existing code that uses LIKE, enable imci_convert_like_to_match to get index acceleration without rewriting queries.

What's next

• Analysis of Full-Text Retrieval Capabilities in Columnstore Indexes