Full Text Search always returns empty result set

Question

I have a table named 'fact' with title column, that should be full text index.

Firstly, I add a full text index:

ALTER TABLE fact ADD FULLTEXT title_fts (title)

So, I insert row:

INSERT INTO fact (id, title) VALUES ('1', 'red blue yellow ok green grey ten first wise form');

And then I perform search:

select * from fact f where contains (f.title, '"red" or "blue"')

When I perform the following query, or any other query with 'contains' statement, I get emtpy result set:

I have to use this statement, not against-match or like. Does anyone happen to have an idea why is this happening? Thank you.

Jiminez

Thats not full text search you need to use match against syntax – Macro

You are completely right. That solved it. Thank you! – Jiminez

Answer 1

There are two really important concepts when using full text search. The first is the minimum work length (see here). By default, the value is 4, meaning that words shorter than this are ignored. Bye-bye "red", "ok", "ten" and other short words.

The second important concept is the stop words list (see here). This would also get rid of "ok" and "first".

Your text doesn't have "blue" and "red" is ignored, so your query doesn't return anything.

You will need to re-build your index after you decide on the words that you really need to include.

Waechter

Thank you a lot! But, when I do the same things with the (only) 5-letter words, that is: select * from fact f where contains (f.title, 'green or yellow'), I still get the empty result set. Should full text index be reconfigured? – Jiminez

@Jiminez . . . You also need to learn about boolean mode versus natural language mode. The structure of your queries suggests that you should stick to boolean mode. – Waechter