How to sort MYSQL fulltext search results by relevancy
LIKE is not fulltext search. In Fulltext search, MATCH(...) AGAINST(...) returns a matching score that can be roughly approximated as relevancy.
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 DESCMore information this can be found in the MySQL Reference Manual.
SELECT * from vocabulary WHERE translation like 'word' union allSELECT * from vocabulary WHERE translation LIKE '%word%' and translation not like 'word' will list exact matches first