MySQL Full-Text search query with MATCH return strange result order-CodePudding

I need to query a table using the following query:

SELECT cards.name, MATCH(`cards.name`) AGAINST("Swiftfoot Boot") AS relevance
FROM cards
WHERE MATCH(`cards.name`) AGAINST("Swiftfoot Boot")
ORDER BY relevance DESC;

Here is my db-fiddle link with schema and records.

If you run the query you can see that the first result with the highest relevance is "Boot Nipper", but I expect to have "Swiftfoot Boots" (note the 's' at the end). Any idea why this strange order result and how to fix it?

CodePudding user response：

Natural Language Full-Text Searches

Every correct word in the collection and in the query is weighted according to its significance in the collection or query. Thus, a word that is present in many documents has a lower weight, because it has lower semantic value in this particular collection. Conversely, if the word is rare, it receives a higher weight. The weights of the words are combined to compute the relevance of the row. This technique works best with large collections.

DEMO