How can I sort MySQL fulltext search results by relevancy?

Sorting MySQL Fulltext Search Results by Relevancy

When working with MySQL, it's essential to understand the difference between LIKE and fulltext search for accurate result sorting.

In the provided query:

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

The LIKE operator is used for partial matching, not fulltext search. To perform a fulltext search, use the MATCH(...) AGAINST(...) syntax.

For example:

$query="SELECT * from `vocabulary` WHERE MATCH(translation) AGAINST('+word')";

The MATCH function returns a matching score that can be used as an approximation of relevancy. This score can be used for sorting the results. For example:

$query="SELECT *, MATCH(translation) AGAINST('+word') AS score FROM `vocabulary` ORDER BY score DESC";

This query sorts the results by relevancy, with the highest-scoring matches appearing first.

The above is the detailed content of How can I sort MySQL fulltext search results by relevancy?. For more information, please follow other related articles on the PHP Chinese website!