On 6/10/07, Ralf Junker <[EMAIL PROTECTED]> wrote:
Hello Scott Hess,
>I've lined up some time to work on fts, again, which means fts3. One
>thing I'd like to include would be to order doclists by some baked-in
>ranking. The idea is to sort to most important items to the front of
>the list, and then you can do queries which limit the number of hits
>and can thus be significantly faster for popular terms. [Note that
>"limit the number of hits" cannot currently be done at the fts layer,
>but I'm thinking on that problem, too.]
Maybe I am missing something here, but I can already rank and
limit a FTS2 search with the help of a simple join:
Just to clarify a bit, the ranking I'm talking about here isn't to
control the output from a select statement, it's to help optimize
queries. Let's say you wanted to run a select which logically looks
something like:
SELECT rowid FROM fts LEFT JOIN aux USING (rowid)
WHERE fts MATCH 't*' ORDER BY score(fts) DESCENDING LIMIT 100;
Without the score() function, this would just return the first 100
matches, which is probably unsatisfactory. With score(), it could
return the 100 best matches.
Unfortunately, since virtual tables do not get insight into the
overall query, this will return ALL of the hits to 't*', then join
against the aux table, then order by score, then limit 100. This is
faster than doing it all manually in the client app, but still is very
slow if 't*' matches, say, every row in an fts database containing a
million rows.
I don't want to have to enhance fts to fully comprehend queries like
this - that would be complicated and error-prone. Instead, I'd like
to add a baked-in rank, with some sort of fts query addition to limit
the result set to a more reasonable scale. For instance, in the above
query, you might limit the fts results to 1000 items, then use more
general SQL to further limit to 100 (or 10) items, or even do that
work in the client itself. To a certain extent you could emulate this
using two tables (one with only the highest-rank items), but that
doesn't scale up to handle complicated fts queries.
-scott
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
