Processing of RDS MySQL full-text retrieval related problems

RDS MySQL Full-text search related issues

1. RDS MySQL support for full-text search

2. RDS MySQL Full-text search related parameters

3. RDS MySQL Full Text Search Chinese support

3.1 MyISAM engine table

3.2 InnoDB engine table

4. Comparison of like '%xxx% ' and full-text search

4.1 Like '%xxx% '

4.2 Full Text Search

5. Ft_query_expansion_limit parameter action

1. RDS MySQL support for full-text search

RDS MySQL 5.5 only MyISAM engine tables support full-text indexing, and the RDS MySQL 5.6 version MyISAM and InnoDB engines support full-text indexing.

2. RDS MySQL Full-Text search related parameters

You can set the following parameters in the console parameter settings.

#	Parameter name	Default value	Minimum value	Maximum Value	Role
1	Ft_min_word_len	4	1	3600	MyISAM engine table Full-text index contains the minimum word length
2	Ft_query_expansion_limit	20	0	1000	MyISAM engine table maximum match number for full-text search using with query expansion
3	Innodb_ft_min_token_size	3	0	16	InnoDB engine table Full-text index contains the minimum word length
4	Innodb_ft_max_token_size	84	10	84	InnoDB engine table Full-text index contains the maximum word length

show global variables like ‘ft_%‘;--查看 MyISAM 引擎表全文检索相关参数show global variables like ‘innodb_ft%‘;--查看 InnoDB 引擎表全文检索相关参数

3. RDS MySQL Full Text search Chinese support

The MyISAM engine table and the InnoDB engine table (with RDS MySQL version 5.6) All support Chinese full-text indexing.

3.1 MyISAM engine table

The MyISAM engine table needs to set Ft_min_word_len to be less than or equal to the minimum length of the word that needs to be retrieved, and it is recommended to recreate the full-text index on the table; for Chinese retrieval, it is recommended to set Ft_min_word_len to 1.

 CREATE TABLE ' my_ft_test_02 ' ( ' id ' int(one)  ' not ' NULL Auto_increment, ' name ' text, PRIMARY KEY (' id '), Fulltext  KEY ' idx_ft_name ' (' name ')) ENGINE=myisam auto_increment=8  DEFAULT CHARSET=utf8;--Rebuild Full-text indexing steps: alter table my_ft_test_02 drop key idx_ft_name; --Delete an existing full-text index alter table my_ft_test_02 add fulltext key idx_ft_name (name); --Re-add full-text index

3.2 InnoDB engine table

The InnoDB engine table needs to set Innodb_ft_min_token_size to be less than or equal to the minimum length of the word that needs to be retrieved, and it is recommended to recreate the existing full-text index on the table; for Chinese search, it is recommended to set Ft_min_word_len to 1 .

 CREATE TABLE ' my_ft_test_01 ' ( ' id ' int(one)  ' not ' NULL Auto_increment, ' name ' text, PRIMARY KEY (' id '), Fulltext  KEY ' idx_ft_name ' (' name ')) ENGINE=InnoDB auto_increment=8  DEFAULT CHARSET=utf8;--Steps to rebuild the full-text index: alter table my_ft_test_01 drop key idx_ft_name; --Delete an existing full-text index alter table my_ft_test_01 add fulltext key idx_ft_name (name); --Re-create full-text index

4. Comparison of like '%xxx% ' and full-text search 4.1 like '%xxx% '

For a small amount of data, query conditions can not be participle , you may consider using like '%xxx% ' to query. However, because a condition like '%xxx% ' cannot use an index on a field, it usually takes longer to execute, consumes more resources, and is recommended for use with other criteria, so that you can use the index of other fields to speed up the query as much as possible.

4.2 Full Text Search

The full-text search itself is matched by words , so the returned results will be different from the way like '%xxx% ' .

5. Ft_query_expansion_limit parameter Action

MySQL Full-Text search supports extended search, please refer to full-text search with query extension

The role of the parameter ft_query_expansion_limit is to specify the maximum number of MyISAM engine tables to use with query expansion for full-text search, as illustrated by an example.

The parameter ft_query_expansion_limit is currently set to 20:

Related records in the current table:

Results of using the query extension:

If the problem is not resolved, please contact after-sales technical support .

JPG change rar

Processing of RDS MySQL full-text retrieval related problems