> 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.
Yes, also while value field can change several times per seconds (thousandths in some cases), it is acceptable to have the ranking be updated at a lower frequency and display somewhat "stale" ranking & values. > This should not be true. You should not be using OFFSET. Your queries should be something like That was with only the "value" field being indexed (so without a rank field), is there a better way than OFFSET in that case ? On Tue, Jan 9, 2018 at 6:21 PM, Simon Slavin <slav...@bigfraud.org> wrote: > 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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
