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". Simon. _______________________________________________ sqlite-users mailing list firstname.lastname@example.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users