List:General Discussion« Previous MessageNext Message »
From:Bjorn Palsson Date:November 11 2002 8:15am
Subject:Re: one word fulltext matching problem...
View as plain text  
At 15:36 2002-11-10 -0800, you wrote:
>>From: "A. J. Maclean" <lists@stripped>
>>
>>I have a query like this:
>>
>>SELECT * FROM bc_posts WHERE MATCH (post_city, post_location, post_details,
>>post_message) AGAINST ('webster');
>>
>>I get the results as follows:
>>
>>webster hall
>>webster
>>webster hall club
>>
>>How come the exact match (i.e. "webster") does not get a higher relevance?
>
>Without ORDER BY clause, results of a SELECT are in arbitrary order. That's 
>just the way SQL works --  there is no "relevance ranking" among the results.

Yes and no. There is relevance ranking but you have to tell MySQL to sort it.
Just copy your MATCH to the ORDER BY and use DESC to get the most relevant
results first. 

Example from the 4.0.0 manual a little enhanced:

mysql> SELECT id, body, MATCH (title,body) AGAINST ( 
-> 'Security implications of running MySQL as root') AS score 
-> FROM articles WHERE MATCH (title,body) AGAINST 
-> ('Security implications of running MySQL as root')
-> ORDER BY score DESC; 
+----+-----------------------------------------------+-----------------+
 | id | body | score | 
+----+-----------------------------------------------+-----------------+
 | 4 | 1. Never run mysqld as root. 2. Normalize ... | 1.5055546709332 |
 | 6 | When configured properly, MySQL could be ... | 1.31140957288 |
 +----+-----------------------------------------------+-----------------+

More to read: 6.9 MySQL Full-text Search

Regards
Thread
one word fulltext matching problem...A. J. Maclean10 Nov
  • Re: one word fulltext matching problem...Octavian Rasnita10 Nov
Re: one word fulltext matching problem...Jan Steinman11 Nov
  • RE: one word fulltext matching problem...A. J. Maclean11 Nov
  • Re: one word fulltext matching problem...Bjorn Palsson11 Nov