Perform a full text search for partial matches where shortest match has the highest relevance in MySQL?

Question

I'd like to perform a fulltext search against a column, looking for a partial word match (IE: against('fra*' in boolean mode)). But I would like to receive results that assign a higher relevancy to shorter words that match.

So, for example, if I perform a search for 'fra' and get the results 'frank', 'fran' and 'frankfurter', I would like them to be ordered in terms of relevancy like this: 'fran', 'frank', 'frankfurter'.

Is there someway this can be achieved?

Answer 1

SELECT 
token FROM tokentable 
WHERE token LIKE '%fra' 
ORDER BY CHAR_LENGTH(token) ASC

to fullfill your example, very fast as well due to btree indexing. you can also do:

SELECT 
document FROM documents
WHERE document LIKE '%frankfurter%' 
AND document LIKE '%würstel%' 
ORDER BY CHAR_LENGTH(document) ASC

but this is probably nonsense of relevancy calculation.

simple answer: if you only do autosuggestion based on prefix, the method above is fine. theres no need to use match against. if you want to search to do fulltext search you cant do it with such a criteria, you should look at lucene/solr