On 9 Jan 2018, at 10:26am, Eric Grange <egra...@glscene.org> 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users