MySQL innodb full-text indexing uses

1. Mysql 5.7 full-text indexing the following parameters (configuration file / etc/my.cnf)

# controls the minimum length of innodb full-text retrieval participle. If set to 2, a Chinese character and a letter will not be found.

Ngram_token_size=1

# the minimum word length stored in the FULLTEXT index of InnoDB. After using ngram_token_size, you don't need innodb_ft_min_token_size, but for insurance, I set both.

Innodb_ft_min_token_size=1

# minimum length of participle, generally modified to 1

Ft_min_word_len = 1

2. Create a table

Mysql > show create table s_test +-+-- + | Table | Create Table | | +-+- -- + | s_test | CREATE TABLE `s_ test` (`id` int (11) NOT NULL DEFAULT'0' COMMENT 'primary key' `uname` varchar (50) DEFAULT NULL COMMENT 'username', `dept` int (11) DEFAULT NULL COMMENT 'department group ID', `info` varchar DEFAULT NULL COMMENT' other information') ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-+-- + 1 row in set (0.00 sec)

3. Create an index

Mysql > create fulltext index ix_ft_s_test_uname_info on s_test (uname,info) WITH PARSER ngram;Query OK, 0 rows affected, 1 warning (2.68 sec) Records: 0 Duplicates: 0 Warnings: 1

4. Query all the indexes of this table

Mysql > show index from s_test +- -+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-+- +- -+ | s_test | 1 | ix_ft_s_test_uname_info | 1 | uname | NULL | 99750 | NULL | NULL | YES | FULLTEXT | | s_test | 1 | ix_ft_s_test_uname_info | 2 | info | NULL | 99750 | NULL | NULL | YES | FULLTEXT | +- +-+-+ 2 rows in set (0.00 sec)

5. Query index details

Mysql > select * from mysql.innodb_index_stats where database_name='mydb' and table_name='s_test' +- -- + | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +- -+ | mydb | s_test | FTS_DOC _ ID_INDEX | 2015-10-03 14:07:18 | n_diff_pfx01 | 100672 | 20 | FTS_DOC_ID | | mydb | s_test | FTS_DOC_ID_INDEX | 2015-10-03 14:07:18 | n_leaf_pages | | NULL | Number of leaf pages in the index | | mydb | s_test | FTS_DOC_ ID_INDEX | 2015-10-03 14:07:18 | size | size | NULL | Number of pages in the index | | mydb | s_test | GEN_CLUST_INDEX | 2015-10-03 14:07:18 | n_diff_pfx01 | 99750 | 20 | DB_ROW_ID | | mydb | s_test | GEN_CLUST_INDEX | | 2015-10-03 14:07:18 | n_leaf_pages | 525 | NULL | Number of leaf pages in the index | | mydb | s_test | GEN_CLUST_INDEX | 2015-10-03 14:07:18 | size | 545 | NULL | Number of pages in the index | +-| -+-+ 6 rows in set (0.00 sec)

6. Full index query

By specifying 1 in the AGAINST () function, IN NATURAL LANGUAGE MODE expr is the string to search for. 2. IN NATURAL MODE WITH QUERY EXPANSION searches with the given phrase for the first time, and uses the given phrase for the second time with some highly relevant rows in the first search results. 3. There are special characters in IN BOOLEAN MODE expr. Special search syntax can only be queried by phrase. You cannot use the intermediate characters mysql > select count (*) from s_test where MATCH (uname,info) AGAINST ('ajar' IN BOOLEAN MODE); +-+ | count (*) | +-+ | 6238 | +-+ 1 row in set (0.02 sec)