Re: general design issue

From: paul c <toledobythesea_at_oohay.ac>
Date: Fri, 24 Apr 2009 18:04:48 GMT
Message-ID: <4bnIl.23980$Db2.6231_at_edtnps83>


skink wrote:
> On 24 Kwi, 18:48, paul c <toledobythe..._at_oohay.ac> wrote:

>> skink wrote:
>>
>> ...
>>
>>> my only suspicion is that OS i'm using (google android) puts
>>> additional layer (i use java iface) in order to access sqlite.
>>> maybe its implementation is somehow done not right way...
>>> thanks again
>> Then the only suggestion I can think of is to use 'explain' and 'explain
>> query plan' on some other table.  Keep trying different queries until
>> you get one that indicates something like "TABLE words WITH INDEX
>> sqlite_autoindex_words_1" to show that an index was used.  Then
>> carefully compare that table's definition with the one that is giving
>> you trouble, try to eliminate any differences even if they seem
>> cosmetic, as you eliminate each difference, run explain query plan
>> again.  My guess is that you will locate one or more differences that
>> are crucial.
>>
>> In other words, confirm that your platform will use an index sometimes.
>>   If it never does, it's a packaging problem.  If it sometimes does,
>> then you know whatever version of sqlite you've got should be capable of
>> optimizing the query.

>
> paul,
>
> to be honest "explain" and "explain query plan" is another thing i was
> not aware of.;)
>
> i used "explain query plan" and in fact GLOB SHOULD use my index, but
> it doesn't
>
> sqlite> explain query plan select distinct substr(key, 5, 1) from
> words where key LIKE "inte%";
> 0|0|TABLE words
> sqlite> explain query plan select distinct substr(key, 5, 1) from
> words where key GLOB "inte*";
> 0|0|TABLE words WITH INDEX wordsIdx
>
> ...

So it looks like your platform will use an index for GLOB and yet you say "it doesn't". It also looks to me that you are using some command-line interface for the above test. If I've got that right then I wonder what your embedded sql calls look like. I'm remembering what D. Richard Hipp said in the quote I posted yesterday:

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.
 >

Is it possible that the way your java code invokes sqlite violates what he says? Received on Fri Apr 24 2009 - 20:04:48 CEST

Original text of this message