MySQL: Something wrong with Fulltext search - Returning NO results

Question

I have a table places(name vachar(50), address text, description text) engine = MYISAM and few records.

I have been trying to do some example in here!, and searching for the 'full-text problem' but i have not clear about fulltext search.

First, i add a fulltext index by:

ALTER TABLE places ADD FULLTEXT(name, address, description);

and try mySQL:

SELECT * FROM places WHERE MATCH(name,address,description) AGAINST('my search key');

i have try some 'my search key' that content is in many field, but there are some case i got:

• now row selected .

• if i trying add option "IN BOOLEAN MODE" in AGAINST(), result obtained with score =1 , its is meaningless when i sort it .

• it just show the result when the "search key" has content in field (name).

i tried a lot way but i don't remember all cases,

Any answer could help!

Thanks in advance!

Answer 1

I had the same issue, the query was working in the past and suddenly my tests started failing. I managed to isolate the query and indeed, it didn't returned any results. I was using boolean mode so the 50% rule could not have caused the issue.

I solved the issue by running:

OPTIMIZE TABLE organizations; # replace table name

I think sometimes the MySQL index stops working when you have a lot of writes / deletes. Weird that I didn't got an error instead.

Answer 2

In addition to Marcus' answer:

When I first started with FULLTEXT indexes in MySQL, I had problems with empty results - I was avoiding stop words and was also using long enough ones, so I was head-scratching a bit until I found this buried in the MySQL documentation:

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

This also explains why you got results (but with a "meaningless" score) when you set the query to operate IN BOOLEAN MODE