Re: general design issue
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Thu, 23 Apr 2009 10:29:55 -0300
Message-ID: <49f06d55$0$5482$9a566e8b_at_news.aliant.net>
>
> ...
>
>
>
>
> 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.
Date: Thu, 23 Apr 2009 10:29:55 -0300
Message-ID: <49f06d55$0$5482$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...
>
>
>
> 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.
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. Received on Thu Apr 23 2009 - 15:29:55 CEST