Thesql MATCH relavancy score

full-text-searchMySQL

I thought this would be an easy question to google for; but I couldn't not find it.

Given the following query:

SELECT name, MATCH (name) AGAINST ('vision black*' IN BOOLEAN MODE) as rel 
FROM (`phppos_items`) 
WHERE MATCH (name) AGAINST ('vision black*' IN BOOLEAN MODE) AND `deleted` = 0
ORDER BY `rel` DESC LIMIT 100

Is the HIGHER match score more relevant? Could someone point me to the right mysql manual page that explains this?

Based on simple observation a higher score does seem better.

Best Answer

MySQL Documentation Under Natural Language Full-Text Searches

Paragraph 1

By default or with the IN NATURAL LANGUAGE MODE modifier, the MATCH() function performs a natural language search for a string against a text collection. A collection is a set of one or more columns included in a FULLTEXT index. The search string is given as the argument to AGAINST(). *For each row in the table, **MATCH() returns a relevance value; that is, a similarity measure between the search string and the text in that row in the columns named in the MATCH() list***.

Paragraph 3

When MATCH() is used in a WHERE clause, as in the example shown earlier, the rows returned are automatically sorted with the highest relevance first. Relevance values are nonnegative floating-point numbers. Zero relevance means no similarity. 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.

When doing Boolean Searches, MATCH() will return 0 or 1.

NOTE TO MyISAM USERS

You can use the myisam_ftdump utility against the MyISAM table to get a complete listing of words and their frequency.

NOTE TO InnoDB USERS

There is a wonderful explanation of how InnoDB calculates Relevance in Boolean Fulltext Searching under the subheading Relevancy Rankings for InnoDB Boolean Mode Search in the MySQL Documentation (Boolean Full-Text Searches)

Related Question