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 <> wrote:

> On 9 Jan 2018, at 9:50am, Eric Grange <> wrote:
> > then I fill that table with something like
> >
> >      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 mailing list

Reply via email to