mysql match against ~ example

Question

Below is an example sql I am stuck with, it will not return a hotel named "mill hotel" It returns 10 other hotels. Any help would be great thanks

---

SELECT * FROM tbl WHERE match(hotel) against('the mill hotel' IN BOOLEAN MODE) LIMIT 10";

---

Answer 1

You need to specify the operators, because not specifying any results in an OR operation. Try this:

SELECT * FROM tbl WHERE match(hotel) against('+the mill hotel' IN BOOLEAN MODE) LIMIT 10";

Read more

Answer 2

You can always use quotes to match the phrase mill hotel rather than the words mill and/or hotel:

SELECT * FROM tbl WHERE match(hotel) against('"the mill hotel"' IN BOOLEAN MODE) LIMIT 10;

or, simply:

SELECT * FROM tbl WHERE match(hotel) against('"mill hotel"' IN BOOLEAN MODE) LIMIT 10;

I believe that all

SELECT * FROM tbl WHERE match(hotel) against('+the mill hotel' IN BOOLEAN MODE) LIMIT 10;

does is require the word the to match, leaving mill and hotel as optional: if you want to require the whole phrase, it needs to be in quotes:

SELECT * FROM tbl WHERE match(hotel) against('+"the mill hotel"' IN BOOLEAN MODE) LIMIT 10;

although when you've only got one search phrase, as in this example, the plus sign is redundant.