MySQL Full Text search increase relevance for exact matches

full-text-searchmariadbMySQL

I have a MySQL database of ~10,000 organisation names that I want to be able to search. I would like to use a full text search because this would enable me to find "institute of doobry" by searching "doobry institute" etc.

The problem I have is that I have lots of entries like "institute of doobry canteen" and "institute of doobry alumni association".

MATCH (names) AGAINST ("doobry institute")

will return all of these records and will not score institute of doobry higher than the canteen.

I'd sort of like to say: score it higher if the word count is similar.

Is there any way to achieve this?

Best Answer

Messy workaround answer:

    SELECT *
      FROM table
     WHERE MATCH ("doobdy institute") AGAINST (name)
  ORDER BY MATCH ("doobdy institute") AGAINST (name),
           ABS( LENGTH("doobdy institute") - LENGTH(name) )
Related Question