MySQL Match Against In Boolean Mode returns nothing on middle word

Question

I've got a problem using Match Against in my MySQL database, and I'm hoping someone can help.

This is the examples of the data in my database:

id   name
1    really bitter chocolate
2    soft cheese

When I run this query:

SELECT * FROM food WHERE (name) LIKE "%bitter%"

This bring back the first result:

1    really bitter chocolate

However its part of a much larger query, and when I run the Match Against code, I don't get anything returned from either of these queries:

SELECT * FROM food WHERE MATCH (name) AGAINST ("bitter")

SELECT * FROM food WHERE MATCH (name) AGAINST ("bitter", IN BOOLEAN MODE)

I have full text searches turned on, and it works when I search the start of the name:

SELECT * FROM food WHERE MATCH (name) AGAINST ("really")

SELECT * FROM food WHERE MATCH (name) AGAINST ("really", IN BOOLEAN MODE)

Both of which returns:

1    really bitter chocolate

I've read through this for solutions: http://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html

And I've looked here: mysql WHERE MATCH AGAINST

Can someone please see where I am going wrong or point me in the right direction?

Thanks!

EDIT

Ok as per Woot4Moo great answer below I've changed my code to remove the comma which shouldn't have been there. I've also added in the + and putting it in single quotes but still no luck.

My current query now looks like this:

SELECT * FROM food WHERE MATCH (name) AGAINST ('+bitter' IN BOOLEAN MODE)

But it's returning no results in query browser, and not returning any errors or warnings.

Answer 1

If this are the only two rows in your table then you have in 50% of the records the searched string and it will be ignored.