Fulltext search not working properly

Question

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?

Answer 1

By default MySQL uses ft_min_word_len=3 setting which tell MySQL full-text engine to ignore all the words LESS than 3 letters. You need to decrease this variable. It's not dynamic so you may need to restart MySQL. However setting it to less than 3 may affect performance.