Hello, one year ago i ask advise to optimize this SQL :
CREATE TABLE HASH( ID INTEGER NOT NULL, x1_y1 SMALLINT NOT NULL, x1_y2 SMALLINT NOT NULL, x1_y3 SMALLINT NOT NULL, x1_y4 SMALLINT NOT NULL, x1_y5 SMALLINT NOT NULL PRIMARY KEY (ID) ); CREATE INDEX HASH_X1_Y1_IDX ON HASH (X1_Y1); CREATE INDEX HASH_X1_Y2_IDX ON HASH (X1_Y2); CREATE INDEX HASH_X1_Y3_IDX ON HASH (X1_Y3); CREATE INDEX HASH_X1_Y4_IDX ON HASH (X1_Y4); CREATE INDEX HASH_X1_Y5_IDX ON HASH (X1_Y5); fill it with 20 millions rows Select ID from HASH where x1_y1 >= <#randomnumber1> - 20 and x1_y1 <= <#randomnumber1> + 20 and x1_y2 >= <#randomnumber2> - 20 and x1_y2 <= <#randomnumber2> + 20 and x1_y3 >= <#randomnumber3> - 20 and x1_y3 <= <#randomnumber3> + 20 and x1_y4 >= <#randomnumber4> - 20 and x1_y4 <= <#randomnumber4> + 20 and x1_y5 >= <#randomnumber5> - 20 and x1_y5 <= <#randomnumber5> + 20; and philippe makowski sugest me this : CREATE ASC INDEX HASH_IDX ON HASH (X1_Y1, X1_Y2, X1_Y3, X1_Y4, X1_Y5); Select ID from HASH where X1_Y1 IN (10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50) and x1_y2 >= <#randomnumber2> - 20 and x1_y2 <= <#randomnumber2> + 20 and x1_y3 >= <#randomnumber3> - 20 and x1_y3 <= <#randomnumber3> + 20 and x1_y4 >= <#randomnumber4> - 20 and x1_y4 <= <#randomnumber4> + 20 and x1_y5 >= <#randomnumber5> - 20 and x1_y5 <= <#randomnumber5> + 20; that was very (very) much efficient (10x more faster) !! this is based on http://explainextended.com/2010/05/19/things-sql-needs-determining-range-cardinality/ now with this strategy i don't remenbered, but did i need to keep the index CREATE INDEX HASH_X1_Y1_IDX ON HASH (X1_Y1); CREATE INDEX HASH_X1_Y2_IDX ON HASH (X1_Y2); CREATE INDEX HASH_X1_Y3_IDX ON HASH (X1_Y3); CREATE INDEX HASH_X1_Y4_IDX ON HASH (X1_Y4); CREATE INDEX HASH_X1_Y5_IDX ON HASH (X1_Y5); or i can remove them ? thanks by advance stéphane
