Subject | Re: [firebird-support] Optimize this simple SQL |
---|---|

Author | Vander Clock Stephane |

Post date | 2012-01-31T18:56:54Z |

> 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 ...

>no, i run ONLY this kind of query (just the number change) :

> 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?

>

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)

>Always thanks for the help :) and i follow always this rule : never look

> Stephane, it seems you have been asking this list to think for you and

> have not tried things for yourself...

>

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]