Ordering FULLTEXT searches on relevance and other fields

Question

I'm using FULLTEXT natural language search, and I notice that it automatically sorts my results by relevance. However, when I start to add things to ORDER, it seems to no longer sort by relevance. Is there a way to explicitly set the importance of relevance sorting?

Answer 1

If you specify the MATCH (...) AGAINST (...) within the column portion of the SELECT query, you can then explicitly order by the 'score' and then any other required parameters.

For example:

SELECT column_a, column_b, MATCH(...) AGAINST (...) AS score
FROM
...
ORDER BY score DESC, column_a DESC

This might seems a bit dubious (as you will have to repeat the same MATCH clause within the WHERE portion of the query to do the actual matching) but it should work perfectly. (If there's a cunning way to alias such things, I'd love to know, but there didn't seem to be in the last version of MySQL I used.)