Re: general design issue
Date: Fri, 24 Apr 2009 12:14:49 -0700 (PDT)
Message-ID: <c3239c9f-dbef-4858-ab97-9bd46de0e0f9_at_k38g2000yqh.googlegroups.com>
On 24 Kwi, 20:04, paul c <toledobythe..._at_oohay.ac> wrote:
> 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?
oh my god, yes, yes, yes !!!
i got it working !!!
this is my code:
StringBuilder sb = new StringBuilder(); long n1 = System.nanoTime(); // String sql = "select distinct substr(key,6,1) from words where key glob 'radio*'"; String sql = "select distinct substr(key,6,1) from words where key glob ?"; String[] selParams = { "radio*" }; // Cursor cursor = mDatabase.rawQuery(sql, null); Cursor cursor = mDatabase.rawQuery(sql, selParams); while (cursor.moveToNext()) { sb.append(cursor.getString(0)).append(" "); } cursor.close(); long n2 = System.nanoTime(); Log.d(TAG, "test " + (n2-n1)/1000000.0 + " ms; result " +sb.toString());
when i run it as it is i got:
test 1456.283462 ms; result a g l t ~
when i switch comments in String sql and Cursor cursor definition i
got:
test 23.635743 ms; result a g l t ~
~60 times faster !!!!
i cant belive it...
paul, thank you, thank you again...
pskink Received on Fri Apr 24 2009 - 21:14:49 CEST