How to Order MySQL Fulltext Search Results by Relevance?

How to Rank MYSQL Fulltext Search Results by Relevance

Question:

How do I order MYSQL fulltext search results by relevance, prioritizing exact matches and sorting partial matches alphabetically?

Answer:

To perform a fulltext search in MYSQL, use the MATCH(...) AGAINST(...) construct instead of the LIKE operator. MATCH(...) AGAINST(...) returns a relevancy score for each result, which can be used to sort the results by relevance.

Here's a modified query that uses fulltext search and sorts the results by relevancy:

SELECT * FROM `vocabulary`
WHERE MATCH(`translation`) AGAINST ('word')
ORDER BY MATCH(`translation`) AGAINST ('word') DESC;

This query will return results with an exact match first, followed by partial matches sorted in descending order of relevance.

Example:

Consider the following vocabulary table:

id	translation
1	word
2	crossword
3	words
4	wordsmith

Running the above query with the search term "word" would return the following results sorted by relevance:

id	translation
1	word
2	crossword
3	words
4	wordsmith

The above is the detailed content of How to Order MySQL Fulltext Search Results by Relevance?. For more information, please follow other related articles on the PHP Chinese website!