Hello,
I've a pretty big table (between 800.000 and 900.000 rows) with the
following schema:
create table relations (rel_id_from integer, rel_id_to integer,
id_old integer, id_new integer, valid_from integer, valid_to integer);
create index idx_relations_idx0 on relations (valid_from, valid_to,
rel_id_from, rel_id_to);
The "idx_relations_idx0" index should speed up queries which select
a relation between 2 points (rel_id_from and rel_id_to) and which is
valid at a particular date (valid_from and valid_to). Both columns,
valid_from and valid_to, hold the number of days elapsed since
01.01.1970 or so.
Normally, a select-statement would look like this (where 10000 is
the current day):
select * from relations where valid_from < 10000 and valid_to > 10000
and rel_id_from = 78 and rel_id_to = 9120;
This kind of statement is slow (takes between 3 and 4 seconds). It seems
that sqlite is doing a full table-scan.
For testing purposes, I've executed the following statement using fixed
(and existing) values for valid_from and valid_to:
select * from relations where valid_from = 9003 and valid_to = 43020
and rel_id_from = 78 and rel_id_to = 9120;
This statement executed within a few milliseconds but is of course of
no use.
So, could anyone make a suggestion how to speed up my query? How to
speed up queries using "<" and ">"?
Greetings, Christian
