On 10/25/2018 11:13 PM, siscia wrote:
Hi all,
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.
Now, if I do a more classic "select value from ranges where start = ?" this
provides 140000 results/second
So I am pretty sure that I am doing something quite wrong.
Do you guys have any idea of what it could be? How can I obtain better
results?
Your query is the same as "start <= ? AND end >= ?". The trouble is that
SQlite can only use the index to optimize one of "start <= ?" or "end >=
?". 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
Dan.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users