Re: general design issue

From: skink <pskink_at_gmail.com>
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

Original text of this message