On 10/26/2018 02:27 PM, siscia wrote:
Hi all,
thanks for your suggestions, unfortunately, I already tried all of them,
except for the rtrees.
Actually, my request for help wasn't complete.
The ranges I am storing in the table are not overlapping.
To make an example in SQL.
The following can be in the dataset:
INSERT INTO ranges(1, 10, 5);
INSERT INTO ranges(15, 29, 8);
INSERT INTO ranges(30, 32, 9);
However, there will never be something like:
INSERT INTO ranges(1, 10, 5);
INSERT INTO ranges(5, 15, 8); -- impossible, overlap with the first one
So all the queries are actually:
`SELECT value FROM ranges WHERE (? BETWEEN start AND end) LIMIT 1`
Now suppose there is an index on start and so we are looking for (start < ?)
What happen could be that we begin from (start = 0) and move up to (start <=
?) which is basically a full scan.
Or we could begin from (start <= ?) and move down towards (start = 0) which
would be optimal.
In SQL, I guess that is:
SELECT value FROM ranges WHERE (? BETWEEN start AND end)
ORDER BY start DESC LIMIT 1
Or, perhaps more efficient for the cases where there is no such range:
SELECT value FROM (
SELECT value, start, end FROM ranges
WHERE start <= ?
ORDER BY start DESC LIMIT 1
) WHERE end >= ?
Dan.
I am afraid that we are hitting the first case, which really is a pity.
Is there a way to suggest to the index how to work on these cases?
Cheers,
Simone
--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
