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,
> why
> > 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 [1]
extracts and stores in [2] or [3], 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

[1] https://www.sqlite.org/lang_analyze.html
[2] https://www.sqlite.org/fileformat2.html#stat3tab
[3] https://www.sqlite.org/fileformat2.html#stat4tab
sqlite-users mailing list

Reply via email to