Create a full-text index for full-text search - AnalyticDB

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

Prerequisites

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

Note

We recommend that you use an AnalyticDB for MySQL cluster of V3.1.4.17 or later.
For information about how to query the minor version of an AnalyticDB for MySQL cluster, see How do I query the version of an AnalyticDB for MySQL cluster?

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`)
) DISTRIBUTED 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

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';

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;
```
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;