Hello,
Full-Text in MySQL 4.1 allows you to calculate a row score using full-text search and calculation from the same table as the full-text.
For example:
SELECT A.field1,B.field2,B.field3,
MATCH(B.name,B.description) AGAINST('Hello World' IN BOOLEAN MODE)+10*B.field2-5*B.field3 AS score
FROM A INNER JOIN B USING (key)
WHERE MATCH(B.name,B.description) AGAINST('Hello World' IN BOOLEAN MODE)+10*B.field2-5*B.field3
ORDER BY score DESC
LIMIT 0,10;
This search is fast, since it doesn't use temporary table, because all fields on the score calculation are from table B. The query time is monotone (grows as you ask for farther results from the start limit).
If I want to add to the score +3*A.field1 then MySQL ends the search with a temporary table that makes him run over all matched rows, which makes the search run slower, and the query time is constant.
Does anyone know if MySQL tends to calculate scoring also from fields out of the full-text table? (Mabye it is available for MySQL 5 even in its beta?)
Please, that's important for me to design my DB.
-thanks, Eli
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
