Re: general design issue
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Thu, 23 Apr 2009 13:12:14 -0300
Message-ID: <49f09360$0$5498$9a566e8b_at_news.aliant.net>
>>paul c wrote:
>>
>>>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...
>>
>>> Fromhttp://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.
>>
>>I would look into collating sequences and maybe give the query a run
>>through explain plan before looking at the function. On its face, the
>>where clause meets the rules of the optimizer for the like keyword and
>>indexes.
Date: Thu, 23 Apr 2009 13:12:14 -0300
Message-ID: <49f09360$0$5498$9a566e8b_at_news.aliant.net>
skink wrote:
> On 23 Kwi, 11:29, Bob Badour <bbad..._at_pei.sympatico.ca> wrote: >
>>paul c wrote:
>>
>>>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...
>>
>>> Fromhttp://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.
>>
>>I would look into collating sequences and maybe give the query a run
>>through explain plan before looking at the function. On its face, the
>>where clause meets the rules of the optimizer for the like keyword and
>>indexes.
> > thanks Bob, > > i'll try to run explain plan for that select. > > btw was my select similar to your solution?
Yes, that is what I was thinking of. How many distinct keys do you have for your prefix? Received on Thu Apr 23 2009 - 18:12:14 CEST