Re: [sqlite] index not used?

[D. Richard Hipp]

Christian Schwarz wrote:
 
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);
 
[...] 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;
 
So, could anyone make a suggestion how to speed up my query? How to 
speed up queries using "<" and ">"?
 
  DROP INDEX idx_relations_idx0;
  CREATE INDEX idx_relations_idx1
     ON relations(rel_id_from, rel_id_to, valid_from);
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565