Index for BETWEEN function in SQLite

Question

I'm working on an analysis application which operates on datasets up to 10,000,000 entries. I'm only writing to this database during initial import, not afterwards. Right now this dataset is stored in SQLite - mostly for speed reasons.

Unfortunately, I find the speed disappointing, mostly because SQLite can't use indexes queries like

SELECT * FROM tbl WHERE time > 0.1 AND time < 1.1 

It will only use an index for the first expression (time > 0.1), and do a full table scan for the second expression (time < 1.1). A typical operation would only need up to 100 rows, but on average, I'm reading through half the database.

Is there a trick to have "double range" indices in SQLite?

note: switching to another DBMS is an acceptable solution, as long as it's at least as fast, embedded and has .NET support.

update I was wrong, indices are actually used if they "sandwich" a range of data. Unfortunately, only one of those indices gets used, so the following query won't get optimized:

SELECT * 
FROM tbl 
WHERE minTime > 0.1 AND minTime < 1.1  AND maxTime > 1.1 AND maxTime < 2.1 

Answer 1

I'm not sure this will necessarily help your speed issue, but have you looked at using the 'BETWEEN' operator? Chances are the optomizer will be kinder about index usage when using it.

SELECT * FROM tbl WHERE time BETWEEN 0.1 AND 1.1

Answer 2

The R*Tree Module?

http://www.sqlite.org/rtree.html