Hi Michael, could you please (re)post the exact create inex statements +primary key you used. For speeding up your query, you need an index on x only but not on id,x. Best Martin
----- Ursprüngliche Mail ---- Von: Michael Wohlwend <[EMAIL PROTECTED]> An: "sqlite-users@sqlite.org" <sqlite-users@sqlite.org> Gesendet: Dienstag, den 26. September 2006, 09:34:00 Uhr Betreff: AW: [sqlite] Performance question -----Ursprüngliche Nachricht----- Von: Dennis Cote [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 22. September 2006 17:07 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Performance question Michael Wohlwend wrote: > But If I do "select data from pictures where (x between high_x and > low_x) and (y between high_y and low_y) then this takes ca. 8 seconds > (!) on wince. > > >>If you are really writing your between clauses as above with the high >>limit first, then they are not doing what you think. The low limit >>should always be given first. Ah, that was a typo, of course the query was "between (low and high)". I changed this to "x > low and x <= high ..." and i got the same result: 1 single query (without bouds-check) takes 5ms, the query with the bounds-check takes ca. 7seconds (there are indices on x and y). I changed the query to (select ... where id in (v1,v2,...)) this was quite fast again, even if the list of values got over 200 elements, but that's not the way I wanted to do it. Maybe sqlite on arm cpus in the current implementation isn't optimized enough. But I have no idea where this huge slowdown comes from. Cheers Michael ___________________________________________________________ Der frühe Vogel fängt den Wurm. Hier gelangen Sie zum neuen Yahoo! Mail: http://mail.yahoo.de
