How to sort MYSQL fulltext search results by relevancy?

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.

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 DESC More information this can be found in the MySQL Reference Manual.

3 When the MATCH command is used in the WHERE clause, MySQL automatically sorts the rows from highest to lowest relevance. – ejunker Feb 11 '10 at 15:08 Thank you, Rich Adams and ejunker for this one. Both very good points.

I had a client climbing down my neck about search relevance and this is a big help. – Volomike Feb 12 '10 at 20:50.

I have been looking at the same problem and not quite found the perfect answer for my situation yet, but this might be useful for you. I'm pretty new to full text searching also so any experts help me out too. I do two MATCH() AGAINST() statements in the select and combine the score from each to form the total relevancy.

Assigning different multipliers allows me to configure the importnace of each set of results. My first MATCH() would check against the literal (or exact) search term using double quotes My second MATCH would check normally. I apply a higher multiplier to the first match so it should have a higher relevancy value if found.

Something like this. SELECT *, ((MATCH(indexes) AGAINST ('"search_terms"' IN BOOLEAN MODE) * 10) + (MATCH(indexes) AGAINST ('search_terms' IN BOOLEAN MODE) * 1.5)) AS relevance FROM ... WHERE ... AND (MATCH (indexes) AGAINST ('"search_terms"' IN BOOLEAN MODE) > 0 OR MATCH (indexes) AGAINST ('search_terms' IN BOOLEAN MODE) > 0) ... ORDER BY relevance DESC If you run use the EXPLAIN function to show how the query works you should find that the extra MATCH() AGAINST() clauses don't actually add any overhead to the query due to the way MySQL works.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.