Fulltext search not working properly
I have pers table and applied combine fulltext index on (prenom, nom) fields. Below are data in that table.
persID prenom nom (pers table)
--------------------------------
116 te te
117 te test test te
Now, I tried to fetch above record using MATCH and AGAINST . Below is my query.
SELECT `Pers`.`persID`, Pers.prenom, Pers.nom
FROM `bdrplus`.`pers` AS `Pers`
LEFT JOIN `bdrplus`.`pers_detail` AS `PersDetail`
ON ( `PersDetail`.`persID` = `Pers`.`persID` )
WHERE `Pers`.`etat` =1
AND `Pers`.`persID` !=55
AND MATCH(`Pers`.`prenom`, `Pers`.`nom`) AGAINST('te*' IN BOOLEAN MODE)
Result
------------------------
persID prenom nom
117 te test test te
Now I tried using mysql LIKE(%..%) operator. Below is the query which I tried.
SELECT `Pers`.`persID`
FROM `bdrplus`.`pers` AS `Pers`
LEFT JOIN `bdrplus`.`pers_detail` AS `PersDetail`
ON ( `PersDetail`.`persID` = `Pers`.`persID` )
WHERE `Pers`.`etat` =1
AND `Pers`.`persID` !=55
AND (Pers.prenom LIKE 'te%' OR Pers.nom LIKE 'te%')
Result
-----------------------
persID prenom nom (pers table)
116 te te
117 te test test te
I don't understand why I am not getting both records using MATCH AGAINST operator in fulltext index query?
The full text search has few settings and one is ft_min_word_len and by default its set to 4, unless you change it to a lesser number a word with lesser than 4 character will not be considered.
In your case its returning correct result since you have
MATCH(Pers.prenom, Pers.nom) AGAINST('te*' IN BOOLEAN MODE)
and its matching te test and test te
You can check the ft_mn_word_len using the following command
show variables like 'ft_%';
If you need to set it less than the default value and allow word length of 2 to be considered you need to change that value.
In debian/ubuntu OS you can change the value of ft_min_word_len by updating the my.cnf file The location of the file is usually under
/etc/mysql
So go to the above folder and type
sudo vi my.cnf
Then in the file check for the block [mysqld] and see if the variable is already there if not then just add the line
ft_min_word_len = 2
Save it and and restart mysql
sudo /etc/init.d/mysql restart
or
sudo service mysql restart
If you already had the full text indexes on a table before doing the above operation, after above changes drop the indexes and rebuilt or you can do a repair the table from mysql terminal something as
repair table table_name quick
上一篇: MySQL中INDEX，PRIMARY，UNIQUE，FULLTEXT之间的区别？
下一篇: 全文搜索无法正常工作