MYSQL MATCH...AGAINST returning no results

Question

I am completely baffled by this one.

I am currently working on an ecommerce website, and have a table full of test products. In this table is a field called 'name' which is of type TEXT and has a FULLTEXT index.

I inserted a few hundred rows of dummy data, every single row was inserted with "test product" (without the quotes) as it's value in the 'name' field.

However, running the following command returns zero results, even though the word 'product' is currently in the name field for every row.

SELECT name FROM Products WHERE MATCH (name) AGAINST ('product')

I have checked the server variables, everything is set to the default values. The min characters are 4, stopwords are default etc. As far as I can tell, product is not a stopword.

If there is any further information that I can provide that might help to find the resolution to this problem let me know.

Many thanks in advance.

Answer 1

From MySQL documentation on Fulltext searches, your search query is considered to be triggering a stop word as the relevant string is present in all the rows.

The 50% threshold has a significant implication when you first try full-text searching to see how it works: If you create a table and insert only one or two rows of text into it, every word in the text occurs in at least 50% of the rows. As a result, no search returns any results. Be sure to insert at least three rows, and preferably many more. Users who need to bypass the 50% limitation can use the boolean search mode

Here is a link to refer for Boolean Full-Text Searches