CODEWITHSUNDEEP
mysqlsqlsearchindexingfull-text-search
PartySoft
PartySoft

Reputation: 2839

Mysql full text index giving same score for exact match and partial, why?

I am having this weird behavior in mysql 5.6 with full text indexes

I am trying to search a patients database and get the exact matches first then partials by score with a Min Score so i don't return 100000+ results just some suggestions to prevent duplicates into a EHR type of app

The problem is that the exact and partial matches have the same score...

Is that an expected behavior? what options do I have?

enter image description here

Upvotes: 1

Views: 1327

Answers (1)

Todd Phillips
Todd Phillips

Reputation: 121

You stated that you had the same score for exact matches and partials, but your example doesn't have any exact matches in it. ("Luis Test" != "Luis Text"). It just has various partial matches.

It's expected behavior because MATCH using NATURAL LANGUAGE MODE is word-oriented only. It doesn't score partial word matches--i.e, similar words don't count towards the relevance value. Since the same number of words are matched in each record and each record has the same total number of words, they each get the same relevance value (score).

"Relevance is computed based on the number of words in the row, the number of unique words in that row, the total number of words in the collection, and the number of documents (rows) that contain a particular word." -- http://dev.mysql.com/doc/refman/5.6/en/fulltext-natural-language.html

If you want fuzzy matching on words, you should look at this SO question for possible alternative matching schemes: How do I do a fuzzy match of company names in MYSQL with PHP for auto-complete?

Upvotes: 2

Related Questions