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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
