Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

On 12 Jan 2018, at 10:31am, Eric Grange wrote: > >> 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

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

> 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

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

On 9 Jan 2018, at 11:35am, Eric Grange 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

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

e some hits that, perhaps, already do this kind of thing. Regards, Bill -Original Message- From: Eric Grange [mailto:zar...@gmail.com] Sent: Tuesday, January 9, 2018 3:51 To: General Discussion of SQLite Database <sqlite-users@mailinglists.sqlite.org> Subject: [sqlite] Efficient

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

Analogous to the percentile solution (it's actually the same thing), you can use a checkpointing table. This has roughly the complexity of SQRT(n) for both read and write. I.E. say you expect to have 1M records and define order based on value then id. You then make a checkpoint table

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

The only way to efficiently do this would be to have counting (range) index b-trees. Since you don't, you're stuck with a O(n) implementation, either on reading or writing. So your solution is as good as it gets, save maybe some implementation particularities. However, you may consider a shift in

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

On Tue, Jan 9, 2018 at 12:35 PM, Eric Grange wrote: > > 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

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

> Do you actually have a need to find the 4512nd rank ? Yes, this is is used to display and check on "neighbors", ie. keys with similar rank. The other very common query is to show the rank for a given key. In both cases, since things are constantly in flux, the absolute rank and neighbor do not

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

On Tue, Jan 9, 2018 at 11:26 AM, Eric Grange wrote: > So the order by is used to control the insertion order, so that the RANK > autoinc primary key ends up with natural rank order But then, if your range queries are based on a rank derived from value, why not index value

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

On 9 Jan 2018, at 10:26am, Eric Grange wrote: > You mean using limit / offset instead ? > > Even with an index on the VALUE column, queries like > >select * from ranked >order by value >limit 10 offset xxx > > become very slow when xxx is great Yeah, to do

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

10:26 To: SQLite mailing list Subject: [External] Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ? You mean using limit / offset instead ? Even with an index on the VALUE column, queries like select * from ranked order by value limit 10

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

You mean using limit / offset instead ? Even with an index on the VALUE column, queries like select * from ranked order by value limit 10 offset xxx become very slow when xxx is great, while select * from ranked order by rank where rank between xxx and xxx+9 are fast

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

On 9 Jan 2018, at 9:50am, Eric Grange wrote: > then I fill that table with something like > > INSERT INTO RANKED > SELECT key, value > FROM ...something rather complex and big... > ORDER BY value desc > > This works well enough, but as the amount of values to

[sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

Hi, I have a problem where I have a large set of (key, value) which I want to sort by value, and then store in a table having (rank, key, value) fields, so that for a given key I can quickly find the rank, or for a given rank range, I can quickly list the keys & values. Since there is no