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 regardless of the value of xxx
Similarly finding the rank of a key becomes sluggish for keys that are not
in the top without
So the order by is used to control the insertion order, so that the RANK
autoinc primary key ends up with natural rank order
On Tue, Jan 9, 2018 at 10:59 AM, Simon Slavin <slav...@bigfraud.org> wrote:
> On 9 Jan 2018, at 9:50am, Eric Grange <zar...@gmail.com> 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
> increases,
> > this feels wasteful to delete everything and then re-insert
> > everything just to adjust the RANK column, also I am running into memory
> > issues as the ORDER BY requires a temporary b-tree
> > which runs into the gigabyte range in some instances.
>
> The ORDER BY clause serves no useful value here. Leave it out. Do your
> sorting when you query the table.
>
> 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
