It seems to me that you may want to consider defining your table as a virtual R* tree based table.
See http://www.sqlite.org/rtree.html On Wed, 2010-12-08 at 19:56 +0300, Vander Clock Stephane wrote: > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
