> 1 - What PLAN does the engine generate for the query? > PLAN (HASH INDEX (HASH_IDX, HASH_IDX, HASH_IDX, HASH_IDX, HASH_IDX, HASH_IDX, HASH_IDX, HASH_IDX, HASH_IDX, HASH_IDX, HASH_IDX, HASH_IDX, HASH_IDX, HASH_IDX, HASH_IDX, HASH_IDX, HASH_IDX, HASH_IDX, HASH_IDX, HASH_IDX, HASH_IDX, HASH_IDX, HASH_IDX, HASH_IDX, HASH_IDX, HASH_IDX, HASH_IDX, HASH_IDX, HASH_IDX, HASH_IDX, HASH_IDX, HASH_X1_Y5_IDX))
replacing the last HASH_X1_Y5_IDX by HASH_X1_Y2_IDX or HASH_X1_Y3_IDX or .. gave "around" the same result in speed even replacing it by all the index at the same time : HASH_X1_Y1_IDX, HASH_X1_Y2_IDX, HASH_X1_Y3_IDX, HASH_X1_Y4_IDX, HASH_X1_Y5_IDX the speed stay around the same ... > > 2 - What other types of queries to do run? Do you ever search for rows > without X1_Y1, like X1_Y2 and X1_Y3 only? > no, i run ONLY this kind of query (just the number change) : Select * from HASH where x1_y1 in (110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140) and x1_y2 >= 110 and x1_y2 <= 130 and x1_y3 >= 160 and x1_y3 <= 180 and x1_y4 >= 20 and x1_y4 <= 40 and x1_y5 >= 110 and x1_y5 <= 130; (the first "in" instead of >= and <= it's a trick given by philippe) > > Stephane, it seems you have been asking this list to think for you and > have not tried things for yourself... > Always thanks for the help :) and i follow always this rule : never look stupid to ask, but look stupid to not ask :) i can promise you i try the stuff a lot :) i even do some demo projects (find the link below), compare the bench with the rtree in sqlite, etc ... http://sourceforge.net/projects/alcinoe/files/alsqlbenchmark/1.01/ :) now here it's mostly that i want to understand the index strategy ... understand how the index on 5 columns can do the work (i can understand how it's help based on 2 columns (X1_Y1, X1_Y2) but not why 5 columns will help (X1_Y1, X1_Y2, X1_Y3, X1_Y4, X1_Y5) thanks again for you help ! stéphane [Non-text portions of this message have been removed]
