How to sort MYSQL fulltext search results by relevancy

Question

Hello,

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

Answer 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

Answer 2

Take a look at this link which deals with FullText searching and sorting

Answer 3

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

Answer 4

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.

Answer 5

I found this page especially useful. All of my tables are InnoDB (for transactions). Fulltext indexes are not supported on InnoDB. This link shows you how to create a temporary table using the MyISAM engine, which supports Fulltext indexes. For extremely large tables, this may not perform well, but for reasonable ones, it should work. I even convert UNSIGNED INT columns to text to support Fulltext searches.

http://dotnetdiscussion.net/2007/09/20/aspnet-and-mysql-how-to-build-a-search-engine-for-your-website-in-3-steps/ (it is mentioned above).

Answer 6

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 dont actually add any overhead to the query due to the way MySQL works.