Re: general design issue

From: paul c <toledobythesea_at_oohay.ac>
Date: Thu, 23 Apr 2009 18:25:17 GMT
Message-ID: <ho2Il.23743$Db2.18539_at_edtnps83>



Bob Badour 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...
>>
>>
>>
>> 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. Column 
has
 >> 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)

So I tried this:

explain query plan SELECT a,b from text where a GLOB 'a19*'

which returned ""0","0","TABLE text WITH INDEX aindex".

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 - 13:25:17 CDT

Original text of this message