All Products
Search

This Product

    AnalyticDB:Create a full-text index

    Document Center

    AnalyticDB:Create a full-text index

    Last Updated:Mar 28, 2026

    Full-text indexes let you run full-text search queries against VARCHAR columns in AnalyticDB for MySQL. This page covers creating, verifying, and dropping full-text indexes on new and existing tables.

    Prerequisites

    Before you begin, ensure that you have:

    • An AnalyticDB for MySQL cluster running V3.1.4.9 or later (V3.1.4.17 or later recommended)

    To check your cluster version, see How do I query the version of an AnalyticDB for MySQL cluster?.

    Limitations

    • A full-text index can only be created on one column at a time. To index multiple columns, run a separate FULLTEXT statement for each column.

    • Full-text indexes are only supported on columns of the VARCHAR type.

    How it works

    Full-text indexes in AnalyticDB for MySQL are applied differently depending on when the data was written:

    • Newly written data — indexed in real time; no additional steps required.

    • Historical data — not indexed automatically. After adding the index, run BUILD TABLE ... force=true; to index existing rows. Queries on historical data are not accelerated until the BUILD task completes.

    Create a full-text index when you create a table

    Syntax

    CREATE TABLE [IF NOT EXISTS] table_name (
  column_name column_type [column_attributes] [column_constraints] [COMMENT 'string'],
  [FULLTEXT [INDEX|KEY] [index_name] (column_name) [index_option]],
  ...
)
table_attribute
[partition_options]
[AS] query_expression
COMMENT 'string'

    Where index_option can be:

    -- Specify the analyzer (optional):
WITH ANALYZER analyzer_name

-- Specify a custom dictionary (optional):
WITH DICT tbl_dict_name

    Parameters

    ParameterDescription
    table_nameName of the table. Must be 1–127 characters (letters, digits, or underscores) and start with a letter or underscore. Use the db_name.table_name format to distinguish tables with the same name in different databases.
    column_nameName of the column. Must be 1–127 characters (letters, digits, or underscores) and start with a letter or underscore.
    column_typeData type of the column. At least one column must be of the VARCHAR type. For supported data types, see Basic data types.
    column_attributesColumn attributes. For details, see CREATE TABLE.
    column_constraintsColumn constraints. For details, see CREATE TABLE.
    FULLTEXTDeclares a full-text index.
    INDEX|KEYKeyword identifier for the index. Either INDEX or KEY is accepted.
    index_nameName of the full-text index.
    analyzer_nameAnalyzer to use for tokenization. If omitted, the default analyzer is used. For available analyzers, see Analyzers for full-text indexes.
    tbl_dict_nameCustom dictionary to use. For details, see Custom dictionaries for full-text indexes.

    Example

    Create a table named tbl_fulltext_name with a full-text index on the content column:

    CREATE TABLE `tbl_fulltext_name` (
  `id` int,
  `content` varchar,
  `keyword` varchar,
  FULLTEXT INDEX fidx_c(`content`),
  PRIMARY KEY (`id`)
) DISTRIBUTED BY HASH(id);

    Verify that the index was created:

    SHOW INDEX FROM tbl_fulltext_name;

    Expected output:

    +-------------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table             | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl_fulltext_name |          0 | PRIMARY       |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| tbl_fulltext_name |          1 | id_0_idx      |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| tbl_fulltext_name |          1 | keyword_2_idx |            1 | keyword     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| tbl_fulltext_name |          1 | fidx_c        |            1 | content     | A         |           0 |     NULL | NULL   |      | FULLTEXT   |         |               |
+-------------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

    The fidx_c entry with Index_type = FULLTEXT confirms the index was created successfully.

    Add a full-text index to an existing table

    Syntax

    ALTER TABLE `table_name` ADD FULLTEXT [INDEX|KEY] index_name (column_name) [index_option]

    Example

    The following steps add a full-text index to an existing table and index its historical data.

    1. Create a table named tbl_fulltext_name:

      CREATE TABLE `tbl_fulltext_name` (
  `id` int,
  `content` varchar,
  `keyword` varchar,
  FULLTEXT INDEX fidx_c(`content`),
  PRIMARY KEY (`id`)
) DISTRIBUTED BY HASH(id) INDEX_ALL = 'N';

    2. Add a full-text index named fidx_k on the keyword column using the standard analyzer:

      If no analyzer is specified, the default analyzer is used. For available analyzers, see Analyzers for full-text indexes.
      ALTER TABLE `tbl_fulltext_name` ADD FULLTEXT INDEX fidx_k(`keyword`) WITH ANALYZER standard;

    3. Build the index for existing historical data. Rows written before step 2 are not searchable until this BUILD task completes.

      BUILD TABLE `tbl_fulltext_name` force=true;

      The time required depends on the amount of data in the table.

    Drop a full-text index

    Syntax

    ALTER TABLE table_name DROP FULLTEXT INDEX index_name;

    Example

    Drop the full-text index fidx_k from tbl_fulltext_name:

    ALTER TABLE `tbl_fulltext_name` DROP FULLTEXT INDEX fidx_k;

    What's next