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?

2 Answers 2


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) )

you should use a levenshtein function (not a default mysql funcion) as second orderby:

FROM table
WHERE MATCH("doobdy institute") AGAINST (name)
ORDER BY MATCH("doobdy institute") AGAINST (name) DESC, 
         levenshtein("doobdy institute", tag) ASC

this is a valid implementation of levenshtein algoritm in mysql: https://stackoverflow.com/questions/4671378/levenshtein-mysql-php

  • FULLTEXT searching, in itself, already throws the Query Optimizer "under the bus". Please don't add levenstein (Don't send a second bus. The dog is already mangled). Mar 27, 2014 at 17:32
  • That looks genius but doesn't work brilliantly: SELECT levenshtein("insitute doobry", "doobry institute canteen"), levenshtein("insitute doobry", "doobry institute"); Gives 15 and 15. It works well in other situations. Mar 29, 2014 at 8:25
  • this is how fulltext works: since "institute" is not a stop word it has same weight of "doobry" in terms of match. so you should decrease importance of "institute" is some ways (for example stripping it before search, or making another columns where you strip your stop words like "institute" then remake a single fulltext key on both fields) Mar 29, 2014 at 15:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.