Hello, on the table :
CREATE TABLE HASH( ID INTEGER PRIMARY KEY ASC, x1_y1 INTEGER, x1_y2 INTEGER, x1_y3 INTEGER, x1_y4 INTEGER, x1_y5 INTEGER, x2_y1 INTEGER, x2_y2 INTEGER, x2_y3 INTEGER, x2_y4 INTEGER, x2_y5 INTEGER, x3_y1 INTEGER, x3_y2 INTEGER, x3_y3 INTEGER, x3_y4 INTEGER, x3_y5 INTEGER, x4_y1 INTEGER, x4_y2 INTEGER, x4_y3 INTEGER, x4_y4 INTEGER, x4_y5 INTEGER, x5_y1 INTEGER, x5_y2 INTEGER, x5_y3 INTEGER, x5_y4 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 x1_y2 >= <#randomnumber4> and x1_y2 <= <#randomnumber4> + 20 and x1_y3 >= <#randomnumber7> and x1_y3 <= <#randomnumber7> + 20 and x1_y4 >= <#randomnumber10> and x1_y4 <= <#randomnumber10> + 20 and x1_y5 >= <#randomnumber13> and x1_y5 <= <#randomnumber13> + 20 and x2_y1 >= <#randomnumber16> and x2_y1 <= <#randomnumber16> + 20 and x2_y2 >= <#randomnumber19> and x2_y2 <= <#randomnumber19> + 20 and x2_y3 >= <#randomnumber22> and x2_y3 <= <#randomnumber22> + 20 and x2_y4 >= <#randomnumber25> and x2_y4 <= <#randomnumber25> + 20 and x2_y5 >= <#randomnumber28> and x2_y5 <= <#randomnumber28> + 20 and x3_y1 >= <#randomnumber31> and x3_y1 <= <#randomnumber31> + 20 and x3_y2 >= <#randomnumber34> and x3_y2 <= <#randomnumber34> + 20 and x3_y3 >= <#randomnumber37> and x3_y3 <= <#randomnumber37> + 20 and x3_y4 >= <#randomnumber40> and x3_y4 <= <#randomnumber40> + 20 and x3_y5 >= <#randomnumber43> and x3_y5 <= <#randomnumber43> + 20 and x4_y1 >= <#randomnumber46> and x4_y1 <= <#randomnumber46> + 20 and x4_y2 >= <#randomnumber49> and x4_y2 <= <#randomnumber49> + 20 and x4_y3 >= <#randomnumber52> and x4_y3 <= <#randomnumber52> + 20 and x4_y4 >= <#randomnumber55> and x4_y4 <= <#randomnumber55> + 20 and x4_y5 >= <#randomnumber58> and x4_y5 <= <#randomnumber58> + 20 and x5_y1 >= <#randomnumber61> and x5_y1 <= <#randomnumber61> + 20 and x5_y2 >= <#randomnumber64> and x5_y2 <= <#randomnumber64> + 20 and x5_y3 >= <#randomnumber67> and x5_y3 <= <#randomnumber67> + 20 and x5_y4 >= <#randomnumber70> and x5_y4 <= <#randomnumber70> + 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 ... Thanks by advance stéphane _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
