Subject Re: [firebird-support] Optimize this simple SQL
Author Vander Clock Stephane
> 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]