mysql ft,在ubuntu上更改mysql ft_min_word

I am trying to implement the full test search. And for this the first thing I did change the value of ft_min_word_len = 2 on /etc/mysql/my.cnf as

[mysqld]

# * Basic Settings

# * IMPORTANT

# If you make changes to these settings and your system uses apparmor, you may

# also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.

ft_min_word_len = 2

Now Saved that and restarted the server.

I am aware that if I already have an index with FULLTEXT in a table I will need to drop the indexes and rebuilt, or repair the table.

But I have created the table as

create table

`comments`

( `id` int(11),

`comment` varchar(200),

`iduser` int(11) ,

`date_added` datetime

)

ENGINE=MyISAM;

ALTER TABLE comments

ADD FULLTEXT INDEX comment_index

(comment);

Then in the above table I have some comments added manually.

When I try to search something as

SELECT * FROM comments where MATCH (comment) AGAINST ('the') ; // "the" is very common word of length to see my test result

It returns 0 rows.

However if I set AGAINST with a word length of 4 it works.

I tried to check the ft_ variables as

mysql> show variables like 'ft_%';

+--------------------------+----------------+

| Variable_name | Value |

+--------------------------+----------------+

| ft_boolean_syntax | + ->

| ft_max_word_len | 84 |

| ft_min_word_len | 2 |

| ft_query_expansion_limit | 20 |

| ft_stopword_file | (built-in) |

+--------------------------+----------------+

Interesting thing is in /etc/mysql/my.cnf I can only see ft_min_word_len but the ft_max_word_len is not there and more importantly the search less than length 4 does not work atall.

This is making me crazy and not sure if there is some other config which is over writing everything and seems like not able locate them either.

Any help would be appreciated.

Mysql Version in my development machine is

mysql Ver 14.14 Distrib 5.1.63, for debian-linux-gnu (i686) using readline 6.2

解决方案

I was able to found the issue and the fix.

The full text search setting was good since I wanted to search with alt east 2 words and I had ft_min_word_len = 2

Now while doing more testing I found it randomly does search for few 2 character words and ignores other.

Here is an example

CREATE TABLE articles (

id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

title VARCHAR(200),

body TEXT,

FULLTEXT (title,body)

);

INSERT INTO articles (title,body) VALUES

('MySQL Tutorial','DBMS stands for DataBase ...'),

('How To Use MySQL Well','After you went through a ...'),

('Optimizing MySQL','In this tutorial we will show ...'),

('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),

('MySQL vs. YourSQL','In the following database comparison ...'),

('MySQL Security','When configured properly, MySQL ...');

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('use');

Empty set (0.00 sec)

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('Use');

Empty set (0.00 sec)

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('you');

Empty set (0.00 sec)

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('the');

Empty set (0.00 sec)

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('vs.');

Empty set (0.00 sec)

But the following works

mysql> SELECT * FROM articles

-> WHERE MATCH (title,body) AGAINST ('run');

+----+-------------------+-------------------------------------+

| id | title | body |

+----+-------------------+-------------------------------------+

| 4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... |

+----+-------------------+-------------------------------------+

So its something else and apparently found its the ft_stopword_file which has a list of words and does not do anything if any search happens with one of them.

So in this case to allow any word search of length at least 2 character long

Set the ft_min_word_len to 2

Then in the mysql config file , for debian /etc/mysql/my.cnf add ft_stopword_file='path/to/stopword_file.txt'

We can leave this file blank if needed.

Oh one more thing once we do the above settings we need to restart mysql and if we change ft_min_word_len then we need to re-built the index or repair the table.