vendredi 5 décembre 2014

Mysql fulltext index search returning weird result


I have ingredient table. I want all those recipes which have certain ingredients. Below is my table structure.



Table(ingredient) - Applied fulltext index on ingredient column.
------------------------------------------------------
ingredientID rcteID ingredient
310 1 Mint Leaves
311 1 Corriender Leaves
312 1 GreenChili


I am trying to fetch above record below fulltext search query but not getting that record.



SELECT `Ingredient`.`ingredientID` , `Ingredient`.`rcteID`
FROM `ingredient` AS `Ingredient`
WHERE MATCH (`Ingredient`.`ingredient`)
AGAINST ('+Mint Leaves +Corriender Leaves +Greenchili' IN BOOLEAN MODE)
AND `Ingredient`.`rcteID`
IN ( 1 )
GROUP BY `Ingredient`.`rcteID`


Why above query is not working for above record?


When I tried below query it worked. just changed searching text.



SELECT `Ingredient`.`ingredientID` , `Ingredient`.`rcteID`
FROM `ingredient` AS `Ingredient`
WHERE MATCH (`Ingredient`.`ingredient`)
AGAINST ('+Greenchili +Mint Leaves +Corriender Leaves' IN BOOLEAN MODE)
AND `Ingredient`.`rcteID` IN ( 1 )
GROUP BY `Ingredient`.`rcteID`

OUTPUT
--------------------
ingredientID rcteID
311 1


Don't understand what's going on. Why first query not returning any result and below query returning result?





Aucun commentaire:

Enregistrer un commentaire