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