Analogous to the percentile solution (it's actually the same thing), you can
use a checkpointing table. This has roughly the complexity of SQRT(n) for
both read and write.
I.E. say you expect to have 1M records and define order based on value then
You then make a checkpoint table (first_rank,value,rec_id) holding every
1000th record in sorted order. So row 1 of checkpoint table coresponds to
the 1000th sorted record.
When you insert/delete a row, you only need to update checkpoints that come
after said row.
When you are searching for row 4521, you do something like:
(table.value=checkpoint.value AND table.id>=checkpoint.id) OR
table.value ASC,table.id ASC
Sent from: http://sqlite.1065341.n5.nabble.com/
sqlite-users mailing list