On Tue, Jan 9, 2018 at 12:35 PM, Eric Grange <egra...@glscene.org> wrote:
> > But then, if your range queries are based on a rank derived from value,
> > not index value directly? You'd still get fast range queries based on
> values, no?
> You get fast value range queries, but rank range queries become slower and
> slower the farther away you get from the top rank.
As I wrote, that can be (greatly IMHO) mitigated by having a partial
mapping from values to ranks,
across the value range, which thus allows to restrict your query to a
value-range that's larger than
the exact rank-range you want, but still narrow enough to be fast.
And another thought is that you may be able to derive than partial mapping
from the stats ANALYZE 
extracts and stores in  or , if you don't want to do it in your app.
(stats on the value column of your
original table, not the ranked "derived" one).
Also, I can't find the name right now, and thus no link sorry, but SQLite
has a way to "mount" an index
as a table, if I recall correctly (mostly for troubleshooting if I
remember), not sure if that's super useful
since you'd probably need access to low-level b-tree info I think estimate
ranks from that I think, the
stat(1|3|4) table info is a better avenue I think. --DD
sqlite-users mailing list