MySQL - Efficient search with partial word match and relevancy score (FULLTEXT)

Question

How can I do a MySQL search which will match partial words but also provide accurate relevancy sorting?

SELECT name, MATCH(name) AGAINST ('math*' IN BOOLEAN MODE) AS relevance
FROM subjects
WHERE MATCH(name) AGAINST ('math*' IN BOOLEAN MODE)

The problem with boolean mode is the relevancy always returns 1, so the sorting of results isn't very good. For example, if I put a limit of 5 on the search results the ones returned don't seem to be the most relevant sometimes.

If I search in natural language mode, my understanding is that the relevancy score is useful but I can't match partial words.

Is there a way to perform a query which fulfils all of these criteria:

• Can match partial words
• Results are returned with accurate relevancy
• Is efficient

The best I've got so far is:

SELECT name
FROM subjects
WHERE name LIKE 'mat%'
UNION ALL
SELECT name
FROM subjects
WHERE name LIKE '%mat%' AND name NOT LIKE 'mat%'

But I would prefer not to be using LIKE.

Bronco

One option is to look for alternative to mysql`s FULLTEXT such as sphinx. sphinxsearch.com/blog/2014/02/07/use-sphinx-with-mysql – Confute

Answer 1

I obtained a good solution in this (somewhat) duplicate question a year later:

MySQL - How to get search results with accurate relevance

Bronco

Answer 2

The new InnoDB full-text search feature in MySQL 5.6 helps in this case. I use the following query:

SELECT MATCH(column) AGAINST('(word1* word2*) ("word1 word1")' IN BOOLEAN MODE) score, id, column 
FROM table
having score>0
ORDER BY score 
DESC limit 10;

where ( ) groups words into a subexpression. The first group has like word% meaning; the second looks for exact phrase. The score is returned as float.

Preoccupation

Why are you searching for 2 groups? Does the match for both groups generate higher score? (it seems not...) – Relevance

Adding * after my search term did it for me. – Disconformity