On 12/8/2010 9:56 AM, Vander Clock Stephane wrote: > Hello, > > on the table : > > CREATE TABLE HASH( > ID INTEGER PRIMARY KEY ASC, > x1_y1 INTEGER, > x1_y2 INTEGER, > ... > x5_y5 INTEGER > ); > CREATE INDEX HASH_X1_Y1_IDX ON HASH (X1_Y1); > CREATE INDEX HASH_X2_Y2_IDX ON HASH (X2_Y2); > CREATE INDEX HASH_X3_Y3_IDX ON HASH (X3_Y3); > CREATE INDEX HASH_X4_Y4_IDX ON HASH (X4_Y4); > CREATE INDEX HASH_X5_Y5_IDX ON HASH (X5_Y5); > > > with millions of rows, how to optimize such query : > > Select > ID > from > HASH > where > x1_y1>=<#randomnumber1> and > x1_y1<=<#randomnumber1>+ 20 and > ... > x5_y5>=<#randomnumber73> and > x5_y5<=<#randomnumber73> + 20; > > because they takes very very lot of time (hourS) to return :( > on other SGBD (like Firebird) with same amount of data > they return immediatly ... >
I usually seem to be wrong when I try to help here, but I keep trying. My guess is that SQLite uses only one index per query (or per table per query or something like that), and so has to do a whole bunch of full table scans (or at least full scans of the remaining rows). Maybe you need a query like: select id from (select id from hash where x1_y1 BETWEEN #randomnumber1 AND (#randomnumber1 + 20)) where x1_y2 BETWEEN #randomnumber4 AND (#randomnumber4 + 20) nested many more levels deep. This might allow using all the indices. HTH, Gerry _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
