'MySQL Boolean full-text search order by relevance

I am trying to write an SQL query that will search for the title of an article from the table "articles_table". I would like the query to return the results in the following order:

  1. results that match the exact search query
  2. results that match with all the words of the query
  3. results that match some words from the search query

I have writter then following query, but the output result is not what I expected it to be

SELECT id, title, MATCH(title) AGAINST ('test*' IN BOOLEAN MODE) as relevance, status, user_created, creation_date
FROM articles_table
WHERE MATCH(title) AGAINT ('test*' IN BOOLEAN MODE)
ORDER BY relevance DESC

For instance, with the above example the output result will be something like

  1. test test test
  2. home middle test
  3. Test
  4. Test slide 1

And the relevance of the results might be even worse, if the search query was something like "17/03 Article". How could I change my query to get more relevant results?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source