CODEWITHSUNDEEP
mysql
dorign
dorign

Reputation: 93

MySQL: Something wrong with Fulltext search - Returning NO results

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:

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

Any answer could help!

Thanks in advance!

Upvotes: 9

Views: 6687

Answers (3)

Simon Backx
Simon Backx

Reputation: 1372

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.

Upvotes: 2

Marcus Adams
Marcus Adams

Reputation: 53850

Ensure that your key word is not a stop word. To disable or modify the stop word list, you'll need access to set system variables and restart the server.

Also, ensure that your key word meets the minimum full text word length. The default minimum length is 4.

Finally, ensure that the word appears in less than 50% of the records. MySQL won't return results where the word appears in 50% or more of the records.

Upvotes: 14

HorusKol
HorusKol

Reputation: 8706

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

Upvotes: 9

Related Questions