no one have an idea how to do such query ?? thanks stéphane
On 12/8/2010 7:56 PM, 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
