Hello,

I have done a benchmark tool to compare Mysql / Firebird / SqlIte3

you can download the tool here :

http://sourceforge.net/projects/alcinoe/files/alsqlbenchmark/1.00/ALSQLBenchmark_1_00.zip/download

Now for this tool i need 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);

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;


In all the combination i do, with around 10 000 000 rows in the Hash
table, it's take around 0.5 seconds to return that is really too slow
compare to SQLite3 (for exemple) that take only 15 ms (thanks to his
rtree index)

His their any "tricks" or "denormalization" to speed up this query under
Firebird ?

thanks by advance
stéphane