Sorting Boolean Fulltext Searches by Relevance in MySQL, Explained?
I've been looking for resources to explain how this query exactly sorts retrieved items by relevance, and haven't been able to find any. Hopefully one of you can explain the logistics of it to me?
SELECT *, MATCH(body, subject) AGAINST ('words' IN BOOLEAN MODE) AS relevance
FROM `messages`
WHERE MATCH(body, subject) AGAINST ('words' IN BOOLEAN MODE)
ORDER BY relevance DESC
In this case, I know that first half of this query searches through the messages.body and messages.subject columns for the search terms "words". It then returns those results, (regardless of the Boolean Operators) in what is essential a "random order" (ordered by what is found first, then found 2nd, and so on).
What I don't understand, however, is how MySQL interprets the WHERE clause and the rest of the query. How does repeating the first half of code reorder the results by relevance?
For example, an ORDER BY clause that sorts a users.user_id column by desc. numerical order MAKES SENSE to me because each row/cell has a clear order (eg - 3 , 2 , 1, and so on)
But how does (going back to the original query) MySQL interpret these "word" results (words, obviously not having any values/numbers/clear-order) and sort them according to relevance?
Is it because the Boolean Full-text Search gives hidden numerical values to these search terms? Like if the AGAINST clause read:
AGAINST ('+apple -macintosh ~microsoft >windows' IN BOOLEAN MODE)
Like "apple" gets a value of 100, "macintosh" a value of -100, "microsoft" a value of 20, and "windows" a value of 40 (to reflect the Operator Effects)?
I know that this is oversimplifying the process (especially when considering if a column contains more than one of these search terms), but that is the best I got.
What I basically need, is a layman-terms explanation of the WHERE clause's (the 2nd half of query code's) effect on the query results as a whole.
链接地址: http://www.djcxy.com/p/75296.html