Relevance method for sorting MYSQL full-text search results

Question

I am relatively new to MYSQL and have a problem that has been bothering me for a while. I've tried searching for answers in various places, but haven't found an acceptable solution yet.

This is the query I currently run to find the best match for a given search term:

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

The results it returns are comprehensive, including all relevant rows. However, they are not sorted in any particular order and I would like when printing the results in PHP to show the exact matches first. Like this:

---

1 | word <-exact match

2 | crossword <-alphabetical partial match /

3 | words

4 | wordsmith

---

Thank you very much for your help in advance.

-macspacejunkie

Answer 1

LIKE is not Full text search. In full-text search, MATCH(...) AGAINST(...) returns a match score that approximates relevance.

Answer 2

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

Exact matches will be listed first