Isn't this really a repeat of this thread... http://sqlite.1065341.n5.nabble.com/how-into-insert-row-into-middle-of-table-with-integer-primary-key-td98629.html
The result of which was, don't try and use the table row order to sort your data. Add a column that defines your sort order and do the sorting on output, not input. I rather liked Jens solution to use a string to define the sort order. (top of second page of thread) Andy Ling -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Eric Grange Sent: Tue 09 January 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 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 --------------------------------------------------------------------------------------- This email has been scanned for email related threats and delivered safely by Mimecast. For more information please visit http://www.mimecast.com --------------------------------------------------------------------------------------- _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
