---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
On Thursday, 25 October, 2018 10:48, Dan Kennedy <danielk1...@gmail.com> wrote:
>On 10/25/2018 11:13 PM, siscia wrote:
>>
>> I am facing an interesting optimization problem.
>>
>> I have a table like this:
>>
>> CREATE TABLE ranges (
>> start int,
>> end int,
>> value int,
>> );
>>
>> The query that I am interested in optimizing is "select value from
>ranges
>> where (? between start and end)"
>>
>> The max performance that I was able to get is 250 results/second
>with a
>> covering index on all three columns.
>And so you might be iterating through a very large set of records
>to extract the ones you want.
>R-tree might work for you:
> https://sqlite.org/rtree.html
create virtual table ranges using rtree(id, minX, maxX, +value);
insert into ranges (minX, maxX, value)
select x.value - y.value, x.value + y.value, x.value
from generate_series as x
join generate_series as y
where x.start = 1 and x.stop = 10000
and y.start = 1 and y.stop = 10000;
-- data insertion is about 10 times slower
select count(*) from ranges where 25 between minX and maxX;
-- 50254399
-- Run Time: real 3.473 user 3.468750 sys 0.000000
select count(*) from ranges where minX <= 25 and 25 <= MaxX;
-- 50254399
-- Run Time: real 3.533 user 3.546875 sys 0.000000
Execution time is not significantly quicker, at least for this data ... but the
"BETWEEN" version is a tad quicker than the devolved version ...
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
