On 9 Jan 2018, at 11:35am, Eric Grange <[email protected]> wrote:
> In both cases, since things are constantly in flux, the absolute rank and
> neighbor do not really matter
> (outside the top ranks), but changes in rank are being looked at.
> i.e. having rank 155k or 154k is not really meaningful in itself, but on
> the other hand
> gaining 1000 "ranks" by going from 155k to 154k is what users are after.
Okay, I see what you mean. You have a special, unusual, case where the ranks
change frequently and you have a genuine need to do things like "give me all
the ranks from 154k to 155k".
That’s a very difficult thing to do quickly. The fastest way to do it would be
different depending on which you do more: change ranks or query ranks.
The cannonical SQL way would be that every time a figure changes you would
change the ranks of all the rows between the two positions. This would take
only two UPDATE commands each time. The data would be up-to-date all the time
and therefore queries would be fast. But if you have million rows this could
involve a lot of number-shuffling and I can see that it might not work out in
the real world.
>> But then, if your range queries are based on a rank derived from value, why
>> not index value directly? You'd still get fast range queries based on
>> values, no?
>
> You get fast value range queries, but rank range queries become slower and
> slower the farther away you get from the top rank.
This should not be true. You should not be using OFFSET. Your queries should
be something like
SELECT * FROM ranked WHERE rank BETWEEN 154000 AND !55000 ORDER BY rank
which should be lightning fast because you have an index on "rank".
Simon.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
