> Hmm... nobody has an answer? Or did my message get eaten by a spam filter
> :(
>
> On Apr 4, 2005 8:02 PM, Cory Nelson <[EMAIL PROTECTED]> wrote:
>> Using the following:
>>
>> create table t_history(time real, ...);
>> create index i_time on t_history(time);
>> select count(*) from t_history where time between
>> julianday('2004-04-01','utc') and julianday('now');
>>
>> It seems the index is never hit. When using >= and <=, the index
>> works. Is there a reason between can't use it?
I don't know how sqlite does it, but most sql databases I know translate
'a between b and c' to '(b <= a and a <= c) or (c <= a and a <= b)'.
That means you would need a reverse index too and you would have to merge
the results. I don't know of a sql optimizer that can solve this, but
maybe somebody knows more.
Gerald
