On 9 Jan 2018, at 10:26am, Eric Grange <[email protected]> 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 do the whole SELECT, it just throws away the
first few entries without reporting them to you.
Do you actually have a need to find the 4512nd rank ? I was expecting you to
just need to need to list the rows in rank order, meaning you would never need
OFFSET.
Okay, if you do need to do things like find the 4512nd rank, and you want to be
able to insert the values without taking the great amount of memory needed for
the ORDER BY,
1) declare an index on the VALUE column (fastest to do the INSERT first then
create the index, but might not matter for your data)
2) do the INSERT without ORDER BY
3) In your programming language, number the rows by doing
SELECT KEY FROM RANKED ORDER BY VALUE
and for each row returned doing an UPDATE for the RANK value.
It’ll be slower, but it won’t take up the huge chunk of memory needed to keep
that index in memory.
Simon.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
