Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

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) )
share|improve this answer
add comment

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

SELECT *
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: http://stackoverflow.com/questions/4671378/levenshtein-mysql-php

share|improve this answer
    
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). –  RolandoMySQLDBA Mar 27 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. –  artfulrobot Mar 29 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) –  Felice Ostuni Mar 29 at 15:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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