1

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?

2 Answers 2

1

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

0

Plus sing means AND operator, - means NOT and space means OR operator. You are probably looking for MySQL to use "Mint Leaves" as a phrase while it takes you search request as "Mint OR Leaves" which leads to unexpected results. http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html

You are probably need to reconstruct your query to be something like "Mint AND Leaves" OR "Corriender AND Leaves" OR "Greenchili" which would be:

 "Mint +Leaves Corriender +Leaves Greenchili"

Or try external search engine that support phrase match like Solr or Sphinx

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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