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?
>Solution :
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.