On Sun, 12 Dec 2004 15:41:48 +0100, Christian Schwarz <[EMAIL PROTECTED]> wrote:
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.
You should note that when you include a large part of an index it might be faster to actually not use an index, WHERE col > 10000 is a typical case.
Also, an index doesn't index all columns individually but merged (at least to my knowledge). This means it might have to check all entries in the index if you use > or <. If you split your index up into 4 indexes it'll probably be faster.
Indexes are not some magic algorithm that make everything instant, improperly used they can cause more harm than good.
Regards, Peter
