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?

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.