Re: general design issue

From: skink <pskink_at_gmail.com>
Date: Thu, 23 Apr 2009 07:57:12 -0700 (PDT)
Message-ID: <4c3f66cf-c5d4-4e04-bfab-18d06ebd7eb3_at_r36g2000vbr.googlegroups.com>



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? Received on Thu Apr 23 2009 - 09:57:12 CDT

Original text of this message