Re: general design issue
Date: Thu, 23 Apr 2009 18:25:17 GMT
Message-ID: <ho2Il.23743$Db2.18539_at_edtnps83>
> 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.
Just for laughs, I tried a few LIKE tests using the Firefox sqlite add-on, with some indexed ascending 'text' values that looked like 'a1000','a1001', 'a1002',...'a1999'. No matter what variations on "LIKE 'a17%'" I tried, Sqlite 'explain query plan' didn't indicate that the index was used. Then I noticed the following post at http://marc.info/?l=sqlite-users&m=119646134929669&w=2 which I gather is from the sqlite author:
(quote)
drh_at_hwaci.com wrote:
> Spiros Ioannou <sivann_at_image.ece.ntua.gr> wrote: >> John Stanton wrote: >>> Sqlite does cater for text searching. Look at FTS. >> This is not text searching. No stemming, etc etc is required. Columnhas
>> exactly 1 word, and the 'LIKE' substring matching is performed at the >> words' first characters (not end-characters). >> Thanks, > > Index the column and use GLOB with a trailing '*' instead > of LIKE with a trailing '%'. Make the search string a > single string literal token in the SQL: > > SELECT * FROM table WHERE xyz GLOB 'abc*'; > > Do not use an expression for 'abc*'. Do not use bound > parameters for 'abc*'. Make it a literal string within > the SQL. If you do these things, then SQLite will use > an index to do the search and it will be very fast. Way > faster than fgrep. > > -- > D. Richard Hipp <drh_at_hwaci.com>
(end quote)
But
explain query plan SELECT a,b from text where substr(a,4,1) GLOB 'a19*'
returned "0","0","TABLE text",.
This didn't surprise me because single-table optimizations are very often quite arbitrary. Also, for all I kno there is some configuration default that makes LIKE use an index or GLOB to use one when a function like substr is on the LHS. Received on Thu Apr 23 2009 - 20:25:17 CEST