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