-----Ursprüngliche Nachricht----- Von: Martin Pfeifle [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 26. September 2006 13:35 An: sqlite-users@sqlite.org Betreff: AW: AW: [sqlite] Performance question
>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 The table looks like: (blobsize between 100 and 8000 bytes, 250000 rows in the table) Create table t1 (x integer, y integer, flag integer, data blob) Create index idx on t1 (x,y,flag) (it doesn't matter if is inlcuded in the index) Takes 5ms on my pda, 100 of those need 500ms: Select data from t1 where x=v1 and y=v1 and flag=f Takes 7sec(!) on pda for a rectangle with 60 blobs: Select data from t1 where (x between xlow and xhigh) and (y between ylow and yhigh) and flag=f Lightning fast: Adding a column xy set to (x << 16|y) and replacing idex with an idx on xy: Select x,y,data from t1 where xy in (xy1,xy2,...) Cheers, Michael
