About the rtree extension, which was the first idea. The extension appears available without any special installation option. This is easier than what is mentioned in https://sqlite.org/rtree.html <https://sqlite.org/rtree.html> chapter 2: "Compiling The R*Tree Module". This chapter may as well be left out?
With test data where the ranges are mostly non-overlapping, the query now runs faster than without rtree. Even though both run within a millisecond rtree is ten times faster. With order by and limit the timing remains superior. But this relies on strictly non-overlapping ranges. Below my test script /* query 1: using rtree built-in extension */ ; create virtual table ranges using rtree(id, minX, maxX, +value); with r as (select 0 as r union all select r+1 from r where r<1000000) insert into ranges (minX, maxX, value) select r*10+1,r*10+10,r*10+5 from r ; select value from ranges where 123456 between minx and maxx ; 123455 Run Time: real 0.000 user 0.000135 sys 0.000018 /* query 2: using index on minx+maxx */ drop table ranges ; create table ranges (minx int, maxx int, value int) ; with r as (select 0 as r union all select r+1 from r where r<1000000) insert into ranges (minX, maxX, value) select r*10+1,r*10+10,r*10+5 from r ; create unique index ranges_minx_maxx on ranges(minx,maxx) ; select value from ranges where 123456 between minx and maxx ; 123455 Run Time: real 0.002 user 0.001415 sys 0.000016 /* query 3: same, assuming non-overlapping ranges */ select value from ranges where 123456 between minx and maxx order by minx desc limit 1 ; 123455 Run Time: real 0.000 user 0.000057 sys 0.000000 _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
