On 9 Jan 2018, at 11:35am, Eric Grange <egra...@glscene.org> 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".

sqlite-users mailing list

Reply via email to