RE: [sqlite] Re: Index not used with "between"
Hi Cory, Regarding: Is there a reason [the sql command] BETWEEN can't use [an index]? Hmm... nobody has an answer? Or did my message get eaten by a spam filter :( I can't answer your question by providing you the reason, but it appears to be a fact that you will want to use separate comparisons and avoid BETWEEN if you wish an index to be used, see: http://www.mail-archive.com/sqlite-users@sqlite.org/msg05030.html Donald Griggs Opinions are not necessarily those of Misys Healthcare Systems nor its board of directors.
Re: [sqlite] Re: Index not used with "between"
> 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
[sqlite] Re: Index not used with "between"
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?
>
> --
> Cory Nelson
> http://www.int64.org
>
--
Cory Nelson
http://www.int64.org
