Re: general design issue
From: paul c <toledobythesea_at_oohay.ac>
Date: Thu, 23 Apr 2009 11:22:44 GMT
Message-ID: <8cYHl.25019$PH1.21079_at_edtnps82>
>
> both answers are yes.
>
> your questions gave me some idea (probably the same as yours):
>
> select distinct substr(key, 5, 1) from words where key like 'inte%'
>
> but it takes ~1000 ms which is not good news...
Date: Thu, 23 Apr 2009 11:22:44 GMT
Message-ID: <8cYHl.25019$PH1.21079_at_edtnps82>
skink wrote:
> On 23 Kwi, 01:19, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
>> skink wrote:
...
>> I know better than to offer design advice online, but the first question >> that pops into my mind is: >> >> Does sqlite have a substring operator? substr(key,1,length(prefix)+1) >> >> Quickly followed by: >> >> Does sqlite support group by or distinct?
>
> both answers are yes.
>
> your questions gave me some idea (probably the same as yours):
>
> select distinct substr(key, 5, 1) from words where key like 'inte%'
>
> but it takes ~1000 ms which is not good news...
From http://www.sqlite.org/optoverview.html
4.0 The LIKE optimization
Terms that are composed of the LIKE or GLOB operator can sometimes be
used to constrain indices. There are many conditions on this use:
...
This suggests the use of 'substr' might have caused the index to be