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 that case ?
Can’t thin
> 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
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 meaningful in itself, but on
quot; I see 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
Subject: [sqlite] Efficient ways to maintaining order rank / row_number
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 (first_rank,
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
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 queries become slower
> 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
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 directly?
You'd still
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 OFFSET SQLite has to d
nuary 2018 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
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
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 be ranked increase
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 ROW_NUMB
14 matches
Mail list logo