PolarDB:IMCI Full-text Index User Guide

PolarDB IMCI allows you to create, modify, or delete inverted indexes by specifying the column comment when creating a table or modifying the column comment through DDL.

1: Create a full-text index

Create a full-text index on the column that requires text search, and choose a tokenizer based on your business needs.

1. Create a test table: Create a table with a column store index.

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

2. (Optional) Modify the full-text index: Use the ALTER TABLE statement to add a full-text index to the title column and specify the search engine mode of the jieba tokenizer.

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

3. (Optional) Verify tokenization results: Before choosing a tokenizer, you can use the dbms_imci.fts_tokenize function to preview how different tokenizers process text.

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

2: Run full-text search queries

After the index is created, IMCI builds it in the background. Once the build is complete, you can run text queries using MATCH...AGAINST or optimized LIKE statements.

1. Use MATCH...AGAINST queries:

   -- 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");

   Use EXPLAIN to view the execution plan. If the FtsTableScan operator appears, the query has hit the full-text index.

   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%")             |
   +----+------------------------+------+-----------------------------------------------------------------+

   Fallback indicates that for incremental data not yet indexed, the system automatically uses LIKE supplementary scans to ensure result completeness.

2. Accelerate LIKE queries: To be compatible with existing application code, IMCI supports automatically converting specific LIKE queries to MATCH...AGAINST for acceleration.

   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%")             |
   +----+------------------------+------+-----------------------------------------------------------------+

   The execution plan also displays the FtsTableScan operator, confirming that the LIKE query has been successfully accelerated.

3. MATCH to LIKE conversion: When no full-text index exists, to ensure the query still runs correctly while leveraging existing indexes as much as possible, IMCI may convert MATCH to LIKE, and the execution plan shows a full table scan.

   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%")                                    |
   +----+----------------------+------+-----------------------------------------------------------------+

3: Manage and monitor indexes

Query the build status and metadata of indexes, and delete indexes when they are no longer needed.

1. Monitor index build progress and status:

   -- View all inverted indexes
   SHOW imci indexes fulltext;
   SELECT * FROM information_schema.imci_fts_indexes;
   
   -- View a specific inverted index
   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 metadata of a specific inverted index
   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 of a specific inverted index
   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 of a specific inverted index
   SELECT * FROM information_schema.imci_fts_index_packs 
   WHERE schema_name='[db_name]' AND table_name='[table_name]' AND column_name='[column_name]';

2. Delete a full-text index: If you no longer need a full-text index on a column, you can delete it by modifying the column's COMMENT.

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

4: Core system variables

Create a custom dictionary table

1. First, define a table with the same structure as the my_fts_dict table. Column names must match those of the my_fts_dict table.

   • type: Tokenizer type. Only the jieba tokenizer (type 2) and the IK tokenizer (type 3) are supported.

   • word: The dictionary word.

   • is_added: Add the word to the custom dictionary (value 1) or remove it (value 2).

   CREATE TABLE my_fts_dict (
     `type` INT UNSIGNED NOT NULL COMMENT '2 for jieba, 3 for ik',
     `word` VARCHAR(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'dict word',
     `is_added` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1 for add, 2 for delete'
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='imci fts user dict';

2. The custom dictionary itself is an InnoDB table. Read and write operations are the same as those for a regular table.

   1. Add a word:

      INSERT INTO my_fts_dict VALUES(2, "PolarDB", 1)

   2. Remove a word:

      INSERT INTO my_fts_dict(type, word, is_added) VALUES(2, "PolarDB", 2);
      or
      DELETE FROM my_fts_dict WHERE type=2 AND word = "PolarDB";

Configure the custom dictionary

• After creating the custom dictionary table, configure the imci_fts_user_dict_table parameter. For example, test/my_fts_dict'.

  SET GLOBAL imci_fts_user_dict_table = 'test/my_fts_dict';

• If imci_fts_user_dict_table is already set, you can also set imci_fts_user_dict_update to ON to trigger a reload of the custom dictionary specified by imci_fts_user_dict_table.

  SET GLOBAL imci_fts_user_dict_update = ON;

  Note

  The new custom dictionary only takes effect on subsequently built inverted indexes. To apply it to existing inverted indexes, you must rebuild the inverted indexes.

Create a custom synonym table

First, define a table with the same structure as the my_synonym_dict table. Column names must match those of the my_synonym_dict table.

• word: The synonym list. Different words are separated by commas.

• type: The synonym type.

  • 0 indicates that all words in the list are synonyms of each other. For example, "db,database,polardb" means "db" <=> "database" <=> "polardb". When you search for "db", "database", or "polardb", all data matching "db", "database", and "polardb" is returned.

  • 1 indicates that the first word in the list is the primary term and the others are its synonyms. For example, "db,database,polardb" means "db" => "database", "polardb". When you search for "db", all data matching "db", "database", and "polardb" is returned. However, when you search for "database", only data matching "database" is returned.

CREATE TABLE my_synonym_dict (
  `word` VARCHAR(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'comma-separated synonyms',
  `type` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0 for equivalent, 1 for mapping'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='imci fts synonym';

The custom synonym table itself is an InnoDB table. Read and write operations are the same as those for a regular table.

• Add a word:

  INSERT INTO my_synonym_dict VALUES("db,database,polardb", 0);
  INSERT INTO my_synonym_dict VALUES("imci,clickhouse,duckdb", 1);

• Remove a word:

  DELETE FROM my_synonym_dict WHERE type=1 AND word = "imci,clickhouse,duckdb";

Configure custom synonyms

After creating the custom synonym table, configure the imci_fts_synonym_table parameter. For example, test/my_synonym_dict, where test is the database name and my_synonym_dict is the table name.

SET GLOBAL imci_fts_synonym_table = 'test/my_synonym_dict';

If imci_fts_synonym_table is already set, you can also set imci_fts_synonym_update to ON to trigger a reload of the custom synonyms specified by imci_fts_synonym_table.

SET GLOBAL imci_fts_synonym_update = ON;

Create a custom stop word table

First, define a table with the same structure as the my_stop_words table. Column names must match those of my_stop_words.

• word: The stop word.

• is_added: Add the word to the custom dictionary (value 1) or remove it (value 0).

CREATE TABLE my_stop_words (
  `word` VARCHAR(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'stop word',
  `is_added` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1 for add, 0 for delete'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='imci fts stop word';

The custom stop word table itself is an InnoDB table. Read and write operations are the same as those for a regular table.

• Add a word:

  INSERT INTO my_stop_words VALUES("am", 1);
  INSERT INTO my_stop_words VALUES("chinese", 1);

• Remove a word:

  INSERT INTO my_stop_words(word, is_added) VALUES("chinese", 0);
  -- or
  DELETE FROM my_stop_words WHERE is_added=1 AND word = "chinese";

Configure custom stop words

After creating the custom stop word table, configure the imci_fts_stop_word_table parameter. For example, test/my_stop_words, where test is the database name and my_stop_words is the table name.

SET GLOBAL imci_fts_stop_word_table = 'test/my_stop_words';

If imci_fts_stop_word_table is already set, you can also set imci_fts_stop_word_update to ON to trigger a reload of the custom stop words specified by imci_fts_stop_word_table.

SET GLOBAL imci_fts_stop_word_update = ON;