MySQL full-text index ____ft_min_word_len

For children's shoes just using the MySQL full-text index search, the first question may be why I search for XX results do not display it? Obviously I have a data inside. There are many reasons for this problem, the most common of which is ft_min_word_len (minimum search length).

Prepare the test data as follows

CREATE TABLE mysql_fulltext (id BIGINT UNSIGNED auto_increment,text_info VARCHAR (+) not null,primary KEY (ID), fulltext (Text_info)) Engine=myisam DEFAULT CHARSET UTF8 COMMENT ' MySQL full-text index test table '; INSERT into ' Mysql_fulltext ' (text_info) VALUES (' A '), (' B '), (' C '), (' AA '), (' BB '), (' CC '), (' AAA '), (' BBB '), (' CCC '), (' AAAA '), (' bbbb '), (' CCCC ');

Search using the following statement

SELECT * from ' Mysql_fulltext ' WHERE MATCH (text_info) against (' a '); SELECT * from ' Mysql_fulltext ' WHERE MATCH (text_info) against (' AA '); SELECT * from ' Mysql_fulltext ' WHERE MATCH (text_info) against (' AAA ');

According to our inertial thinking, four data should be displayed, but the result is not a single piece of data.

But we do.

SELECT * from ' Mysql_fulltext ' WHERE MATCH (text_info) against (' AAAA ');

But the result is as follows:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/5A/68/wKiom1T9AeDgHDyJAAAXCki0VZE494.jpg "title=" QQ picture 20150309101336.png "alt=" Wkiom1t9aedghdyjaaaxcki0vze494.jpg "/>

Let's take a look at the parameter settings related to full-text indexing.

SHOW GLOBAL VARIABLES like '%ft_% ';

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/5A/64/wKioL1T9Ab_xjt5FAACi7QKAEtg283.jpg "title=" Baidushurufa_2015-3-9_10-8-14.png "alt=" Wkiol1t9ab_xjt5faaci7qkaetg283.jpg "/>

5.6 Before the MyISAM full-text index, the parameters need to be adjusted relatively small, only the above five.

Ft_boolean_syntax: Full-text index word breaker keyword, cannot change, for built-in variable Ft_max_word_len: Maximum word length, generally do not need to change Ft_min_word_len: Minimum word length, generally modified to 1ft_query_ Expansion_limit: A parameter of unknown sensation, basically do not need to set Ft_stopword_file: Full text stop word

We found that Ft_min_word_len is 4, which is the default value. In other words, MySQL fulltext only indexes 4 words or 4 characters or more. And just search for ' a ', ' AA ', ' AAA ' keyword lengths are less than 4.

Full-text index related parameters can not be dynamically modified, we need to add parameter Ft_min_word_len=1 in/etc/my.cnf, and restart the MySQL server, after the restart is complete, the corresponding table to perform repair operation. In theory, it is necessary to restart the server and fix all the tables that contain the Fulltext index when it comes to Ft_ related parameter modification.

Use the following SQL statement to find the table containing the Fulltext index

SELECT DISTINCT CONCAT (' Repair table ', Table_schema, '. ', table_name, ' quick; ') From Information_schema. ' STATISTICS ' WHERE index_type= ' fulltext '

After we fix the Mysql_fulltext, execute the query statement

REPAIR TABLE Mysql_fulltext QUICK; SELECT * from ' Mysql_fulltext ' WHERE MATCH (text_info) against (' a ');

The above query, can query the corresponding data.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/5A/64/wKioL1T9Bo_TuEPuAAAYuQ3Frgk261.jpg "title=" Baidushurufa_2015-3-9_10-28-32.png "alt=" Wkiol1t9bo_tuepuaaayuq3frgk261.jpg "/>

But the question came, why I query the keyword ' a ', but can not search out ' AA ', ' aaa ', ' AAAA ' keyword corresponding to the line?

Interested children's shoes can think for yourself, or follow my subsequent MySQL full-text indexing related articles.

This article is from the "original" blog, please be sure to keep this source http://imysqldba.blog.51cto.com/1222376/1618487

MySQL full-text index ____ft_min_word_len