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
id.
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:
SELECT
FROM
table
JOIN
checkpoint
WHERE
(
(table.value=checkpoint.value AND table.id>=checkpoint.id) OR
table.value>checkpoint.value
) AND
checkpoint.first_rank=4500
ORDER BY
table.value ASC,table.id ASC
LIMIT 21,1
--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
