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?
According to the MySQL Documentation on FULLTEXT Boolean Searching


  "
  
  A phrase that is enclosed within double quote (“"”) characters matches
  only rows that contain the phrase literally, as it was typed. The
  full-text engine splits the phrase into words and performs a search in
  the FULLTEXT index for the words. Nonword characters need not be
  matched exactly: Phrase searching requires only that matches contain
  exactly the same words as the phrase and in the same order. For
  example, "test phrase" matches "test, phrase".
  
  If the phrase contains no words that are in the index, the result is
  empty. For example, if all words are either stopwords or shorter than
  the minimum length of indexed words, the result is empty.
  
  The following examples demonstrate some search strings that use
  boolean full-text operators:
  
  'apple banana'
  
  Find rows that contain at least one of the two words.
  
  '+apple +juice'
  
  Find rows that contain both words.
  
  '+apple macintosh'
  
  Find rows that contain the word “apple”, but rank rows higher if they
  also contain “macintosh”.
  
  '+apple -macintosh'
  
  Find rows that contain the word “apple” but not “macintosh”.
  
  '+apple ~macintosh'
  
  Find rows that contain the word “apple”, but if the row also contains
  the word “macintosh”, rate it lower than if row does not. This is
  “softer” than a search for '+apple -macintosh', for which the presence
  of “macintosh” causes the row not to be returned at all.
  
  '+apple +(>turnover 
  
  Find rows that contain the words “apple” and “turnover”, or “apple”
  and “strudel” (in any order), but rank “apple turnover” higher than
  “apple strudel”.
  
  'apple*'
  
  Find rows that contain words such as “apple”, “apples”, “applesauce”,
  or “applet”.
  
  '"some words"'
  
  Find rows that contain the exact phrase “some words” (for example,
  rows that contain “some words of wisdom” but not “some noise words”).
  Note that the “"” characters that enclose the phrase are operator
  characters that delimit the phrase. They are not the quotation marks
  that enclose the search string itself.


MAIN PROBLEM


You are searching for 3 ingredients
You are querying for 4 words (Mint, Leaves, Corriender, Greenchili)


COURSE OF ACTION

You need to adjust the MATCH clause to query three string tokens. You do that by putting double quotes around "Mint Leaves" and "Corriender Leaves":

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`


GIVE IT A TRY !!!

NOTE: Corriender should be spelled Coriander