Hi.
At 15:41 12/12/2004 +0100, you 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;
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.
I'd suggest reading the 2004 International PHP Conference SQLite slideshow (see the new section of the SQLite site, or just go to http://www.sqlite.org/php2004/page-001.html); specifically page 49 and on (http://www.sqlite.org/php2004/page-049.html). The discussion about the index structure is an eye-opener. Following this, I think you can try to reverse your index and your query to get much better results, at least if you search rel_id_from and rel_id_to as your test query does:
create index idx_relations_idx0 on relations (rel_id_from, rel_id_to, valid_from, valid_to);
select * from relations where rel_id_from = 78 and rel_id_to = 9120 and valid_from < 10000 and valid_to > 10000;
As noted in that page, the inequality should be in the right-most columns whenever you can.
Guy
