All Products
Search
Document Center

AnalyticDB for MySQL:Create a full-text index

Last Updated:Mar 18, 2024

This topic describes how to create a full-text index for full-text query in AnalyticDB for MySQL.

Prerequisites

An AnalyticDB for MySQL cluster of V3.1.4.9 or later is created.

Note

Limits

  • A full-text index can be created only on a single column each time. If you want to create a full-text index for multiple columns, you can create the full-text index on each column.

  • Full-text indexes can be created only on columns of the VARCHAR type.

Visibility policies

  • Full-text indexes of AnalyticDB for MySQL are visible to newly written data in real time.

  • To create a full-text index on historical data, you must execute the BUILD TABLE `Table name` force=true; statement.

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'

Parameters

Parameter

Description

table_name

The name of the table.

The table name must be 1 to 127 characters in length and can contain letters, digits, and underscores (_). The table name must start with a letter or underscore (_). Specify the table name in the db_name.table_name format to distinguish tables that share the same name across different databases.

column_name

The name of the column to be added to the table.

The column name must be 1 to 127 characters in length and can contain letters, digits, and underscores (_). The column name must start with a letter or underscore (_).

column_type

The data type of the column. At least one column must be of the VARCHAR type.

For more information about the data types supported by AnalyticDB for MySQL, see Basic data types.

column_attributes

The attributes of the column. For more information about the column attributes supported by AnalyticDB for MySQL, see CREATE TABLE.

column_constraints

The constraints of the column. For more information about the column constraints supported by AnalyticDB for MySQL, see CREATE TABLE.

FULLTEXT

Defines the full-text index.

INDEX|KEY

The keyword identifier of the full-text index. It can be INDEX or KEY.

index_name

The name of the full-text index.

column_name

The name of the column for creating the full-text index. The column must be of the VARCHAR type.

index_option

Specifies the analyzer and custom dictionary used for the full-text index.

  • WITH ANALYZER analyzer_name: specifies the analyzer used for the full-text index. For more information about the analyzers supported by AnalyticDB for MySQL, see Analyzers for full-text indexes.

  • WITH DICT tbl_dict_name: specifies the custom dictionary used for the full-text index. For more information about the custom dictionaries supported by AnalyticDB for MySQL, see Custom dictionaries for full-text indexes.

Examples

Create a full-text index on the content column when you 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`)
) DISTRIBUTE BY HASH(id);

Query the index of the table.

SHOW index from tbl_fulltext_name;

Sample result:

+-------------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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   |         |               |
+-------------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Create a full-text index for an existing table

Syntax

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

Parameters

For more information about the parameters, see the "Create a full-text index when you create a table" section of this topic.

Examples

  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`)
    ) DISTRIBUTE BY HASH(id) INDEX_ALL = 'N';
  2. Create a full-text index named fidx_k on the keyword column and specify the standard analyzer.

    Note

    If no analyzer is specified, the default analyzer is used. For more information, see Analyzers for full-text indexes.

    ALTER TABLE `tbl_fulltext_name` ADD FULLTEXT INDEX fidx_k(`keyword`) WITH ANALYZER standard;
  3. To create a full-text index on historical data, execute the following statement. Indexes take effect only after BUILD tasks are completed. The amount of time it takes to create a full-text index is related to the amount of data.

    BUILD TABLE `tbl_fulltext_name` force=true;

Delete a full-text index

Syntax

ALTER TABLE table_name DROP FULLTEXT INDEX index_name;

Examples

Delete the full-text index fidx_k from the tbl_fulltext_name table.

ALTER TABLE `tbl_fulltext_name` DROP FULLTEXT INDEX fidx_k;