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>



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:

  1. The left-hand side of the LIKE or GLOB operator must be the name of an indexed column.

...

This suggests the use of 'substr' might have caused the index to be ignored. Maybe it's still possible to write several statements so that the index is used. Just guessing. Received on Thu Apr 23 2009 - 06:22:44 CDT

Original text of this message