How to sort MYSQL fulltext search results by relevancy

Solution 1:

LIKE is not fulltext search. In Fulltext search, MATCH(...) AGAINST(...) returns a matching score that can be roughly approximated as relevancy.

Solution 2:

You can get a good relevance search by creating a fulltext index and then matching against your search term.

So something like this should work.

ALTER TABLE `vocabulary` ADD FULLTEXT INDEX `SEARCH`(`translation`);

SELECT *, MATCH(translation) AGAINST ('+word' IN BOOLEAN MODE) AS relevance 
FROM `vocabulary`
WHERE MATCH(translation) AGAINST ('+word' IN BOOLEAN MODE)
ORDER BY relevance DESC

More information this can be found in the MySQL Reference Manual.

Solution 3:

SELECT * from vocabulary 
WHERE translation like 'word'  
union all
SELECT * from vocabulary 
WHERE translation LIKE '%word%' and translation not like 'word'  

will list exact matches first