How to sort MYSQL fulltext search results by relevancy

Question

I am relatively new to MYSQL and have had an issue that has been bugging me for a while. I've tried googling all over the place for the answer, but have unable to find an acceptable solution as of yet.

Here is the query I am running currently to find the best possible match for a given search term:

$query="SELECT * from `vocabulary` WHERE translation = 'word' OR translation LIKE '%word%'";

The results it returns are comprehensive in that they include all relevant rows. However, they are not sorted in any particular order, and I would like to have the ones with an exact match displayed first when I print results in PHP. Like this:

---

1 | word <-exact match
2 | crossword <- partial matches sorted alphabetically /
3 | words
4 | wordsmith

---

Thank you very much in advance for your assistance.

-macspacejunkie

Solution 1

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

Solution 2

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

Solution 3

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 4

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 importance 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 use EXPLAIN to examine 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.

Solution 5

Your query needs only a little modification to get the order you're looking for.

SELECT * 
FROM vocabulary
WHERE translation LIKE '%word%'
ORDER BY translation <> 'word', translation;

If translation is exactly 'word', it will be at the top of the results. This is because translation <> 'word' will be 0 when there is an exact match which comes before the 1 that will be returned for all the other results. The remaining results will be sorted alphabetically after that because of the , translation.

This query avoids making two queries like the selected answer does with its UNION. Additionally, your query does not need translation = 'word' OR translation LIKE '%word%' since the second half will always be executed and is a superset of the first part.

For those looking for an answer that uses an actual fulltext search, please see the other, more highly upvoted answers.

View more solutions