Problems and Solutions of full-text search in apsaradb RDS for MySQL -

Introduction

This article describes the problems and solutions of full-text search in apsaradb RDS for MySQL.

Background

Alibaba Cloud reminds you that:

Before you perform operations that may cause risks, such as modifying instance configurations or data, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.

You can modify the configurations and data of instances including but not limited to Elastic Compute Service (ECS) and Relational Database Service (RDS) instances. Before the modification, we recommend that you create snapshots or enable RDS log backup.

If you have authorized or submitted security information such as the logon account and password in the Alibaba Cloud Management console, we recommend that you modify such information in a timely manner.

This article introduces the full-text search through the following aspects.

Full-text search support in RDS MySQL
Full-text search parameters for apsaradb RDS for MySQL
Support for Chinese full-text search in RDS MySQL
Comparison between like and full-text search
ft_query_expansion_limit parameters

Full-text search support in RDS MySQL

The RDS MySQL 5.5 version only supports full-text index for MyISAM tables. The RDS MySQL 5.6 versions for both MyISAM and InnoDB tables support full-text index.

Full-text search parameters for apsaradb RDS for MySQL

View the parameters based on the following SQL statement.
1. Run the following SQL statement to view the parameters of full-text search for MyISAM tables:
```
show global variables like 'ft_%';
```
2. Execute the following SQL statement to view parameters of full-text search in InnoDB tables:
```
show global variables like 'innodb_ft%';
```

You can set the following parameters on the parameter settings page of the RDS console.

#	Parameter	The default value.	Minimum value	Maximum value	Function
1	ft_min_word_len	4	1	3600	The minimum length of words to full-text index in the MyISAM table.
2	ft_query_expansion_limit	20	0	1000	The maximum number of matches for a MyISAM table to perform a full-text search using the `with query expansion`.
3	innodb_ft_min_token_size	3	0	16	The minimum token size for full-text indexes in an InnoDB table.
4	innodb_ft_max_token_size	84	10	84	The maximum token size for full-text indexes in an InnoDB table.

Support for Chinese full-text search in RDS MySQL

Both MyISAM tables and InnoDB tables (RDS MySQL 5.6 versions) support Chinese full-text search.

MyISAM engine table

For the MyISAM table, you must set the ft_min_word_len to be less than or equal to the minimum word-breaking length to be retrieved. After the setting is completed, we recommend that you recreate the existing table full-text index. For Chinese retrieval, we recommend that you set the ft_min_word_len to 1. Otherwise, no data may be found.

Example of rebuilding a full-text index

The table structure is as follows.

CREATE TABLE `my_ft_test_02` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` text,
PRIMARY KEY (`id`),
FULLTEXT KEY `idx_ft_name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

Follow these steps to recreate a full-text index:

Execute the following SQL statement to drop an existing full-text index.
```
alter table my_ft_test_02 drop key idx_ft_name;
```

Execute the following SQL statement to add a full-text index.

alter table my_ft_test_02 add fulltext key idx_ft_name (name);

InnoDB engine table

You need to set the innodb_ft_min_token_size of an InnoDB table to be less than or equal to the minimum document length that needs to be retrieved. After the setting is completed, we recommend that you recreate the existing table full-text index. For Chinese characters, we recommend that you set the ft_min_word_len to 1. Otherwise, some data may not be retrieved.

Example of rebuilding a full-text index

The table structure is as follows.

CREATE TABLE `my_ft_test_01` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `idx_ft_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

Follow these steps to recreate a full-text index:

Execute the following SQL statement to drop an existing full-text index.
```
alter table my_ft_test_01 drop key idx_ft_name;
```

Execute the following SQL statement to add a full-text index.

alter table my_ft_test_01 add fulltext key idx_ft_name (name);

Comparison between like and full-text search

like statement

If the data volume is small and the query conditions cannot be split, you can use like to perform the query. However, the like condition cannot use indexes on fields, and therefore it takes a long time to execute, consuming many resources. It is recommended to be used in conjunction with other conditions for the query, so that you can use indexes of other fields to accelerate the query whenever possible.

Full-text search

Full-text retrieval itself matches words. Therefore, the returned results are different from those displayed on the like tab.

ft_query_expansion_limit parameters

MySQL full-text search supports extended search. For more information, see full-text search with query extended. The ft_query_expansion_limit parameter is used to specify the maximum number of matches in MyISAM tables that are searched by using the with query expansion command. The following example shows how to use this parameter.

Run the following SQL statement to confirm that ft_query_expansion_limit parameter value is currently set to 20:
```
show global variables like 'ft_qu%';
```
Run the following SQL statement to view the records in the current table.
```
select * from my_ft_test_01 where id >= 117;
```
The command output is as follows: good and Hello are both displayed 20 times.
Run the following SQL statement to view the results of using query extension:
```
select * from my_ft_test_01 where math(name) agenst ('sunwukong'with query expansion);
```
The command output is as follows. The returned results include good.

Application scope

ApsaraDB RDS for MySQL