FullText Relevance sorting

[leegold]

Given the SQL shouldn't the records be sorting by score ie. relevance? I
thought if "WHERE MATCH" is used it will sort that way. Thanks

mysql> SELECT `page`.`title` , MATCH ( `title` , `descrip` ) AGAINST (
'+code+nasdac' IN BOOLEAN MODE ) AS SCORE FROM `page` WHERE MATCH (
`title` , `descrip` ) AGAINST ( '+code+nasdac' IN BOOLEAN MODE );
+---------------------------------------------+-----------------+
| title | SCORE |
+---------------------------------------------+-----------------+
| Code 99 Specifications | 1.3333333730698 |
| Centralized Management System | 1 |
| Report Number Series | 1.3333333730698 |
| Reviewing Documents for Release | 1 |
+---------------------------------------------+-----------------+
4 rows in set (0.00 sec)

[Bill Karwin]

Re: FullText Relevance sorting

leegold wrote:[color=blue]
> Given the SQL shouldn't the records be sorting by score ie. relevance? I
> thought if "WHERE MATCH" is used it will sort that way.[/color]

http://dev.mysql.com/doc/mysql/en/fulltext-boolean.html

"[Boolean full-text searches] do not automatically sort rows in order of
decreasing relevance."

When you use BOOLEAN MODE, the relevance value returned is always 1.0.
So the sort order becomes unpredictable.

There's a user comment (by Patrick O'Lone) on the following web page
that explains this and gives an example of a workaround:

http://dev.mysql.com/doc/mysql/en/fulltext-search.html

Regards,
Bill K.