0

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?

Jimit
  • 2,201
  • 7
  • 32
  • 66

1 Answers1

0

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

Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
  • What was your early `ft_min_word_len`, did you restart mysql after change ? Also after changing you need to drop the index and re-create it. Or need to repair the table. Also note that mysql full text search has stop word list http://dev.mysql.com/doc/refman/5.1/en/fulltext-stopwords.html – Abhik Chakraborty Dec 05 '14 at 11:49
  • I am using windows so, I checked in my.ini. ft_min_word_len is not available there. I added it and assigned it to 2 then restart mysql and reindex fulltext index – Jimit Dec 05 '14 at 11:54
  • In windows I guess its somewhere `C:\Windows\my.ini` and while adding it make sure that you have `[mysqld]` if not then add that above the line. Then restart the mysql server. Drop the indexes and re-create – Abhik Chakraborty Dec 05 '14 at 11:58