thanks ann i will thing of it :)

now that you are here and i know you also know a little of mysql,
i want to do a benchmark between Firebird and Mysql on a simple
query :

CREATE TABLE HASH(
ID INTEGER PRIMARY KEY ASC,
x1_y1 INTEGER,
x1_y2 INTEGER,
x1_y3 INTEGER,
x1_y4 INTEGER,
x1_y5 INTEGER,
x2_y1 INTEGER,
x2_y2 INTEGER,
x2_y3 INTEGER,
x2_y4 INTEGER,
x2_y5 INTEGER,
x3_y1 INTEGER,
x3_y2 INTEGER,
x3_y3 INTEGER,
x3_y4 INTEGER,
x3_y5 INTEGER,
x4_y1 INTEGER,
x4_y2 INTEGER,
x4_y3 INTEGER,
x4_y4 INTEGER,
x4_y5 INTEGER,
x5_y1 INTEGER,
x5_y2 INTEGER,
x5_y3 INTEGER,
x5_y4 INTEGER,
x5_y5 INTEGER
);
CREATE INDEX HASH_X1_Y1_IDX ON HASH (X1_Y1);
CREATE INDEX HASH_X2_Y2_IDX ON HASH (X2_Y2);
CREATE INDEX HASH_X3_Y3_IDX ON HASH (X3_Y3);
CREATE INDEX HASH_X4_Y4_IDX ON HASH (X4_Y4);
CREATE INDEX HASH_X5_Y5_IDX ON HASH (X5_Y5);


with millions of rows, how to optimize such query :

Select
ID
from
HASH
where
x1_y1>=<#randomnumber1> and
x1_y1<=<#randomnumber1>+ 20 and
x1_y2>=<#randomnumber4> and
x1_y2<=<#randomnumber4> + 20 and
x1_y3>=<#randomnumber7> and
x1_y3<=<#randomnumber7> + 20 and
x1_y4>=<#randomnumber10> and
x1_y4<=<#randomnumber10> + 20 and
x1_y5>=<#randomnumber13> and
x1_y5<=<#randomnumber13> + 20 and
x2_y1>=<#randomnumber16> and
x2_y1<=<#randomnumber16> + 20 and
x2_y2>=<#randomnumber19> and
x2_y2<=<#randomnumber19> + 20 and
x2_y3>=<#randomnumber22> and
x2_y3<=<#randomnumber22> + 20 and
x2_y4>=<#randomnumber25> and
x2_y4<=<#randomnumber25> + 20 and
x2_y5>=<#randomnumber28> and
x2_y5<=<#randomnumber28> + 20 and
x3_y1>=<#randomnumber31> and
x3_y1<=<#randomnumber31> + 20 and
x3_y2>=<#randomnumber34> and
x3_y2<=<#randomnumber34> + 20 and
x3_y3>=<#randomnumber37> and
x3_y3<=<#randomnumber37> + 20 and
x3_y4>=<#randomnumber40> and
x3_y4<=<#randomnumber40> + 20 and
x3_y5>=<#randomnumber43> and
x3_y5<=<#randomnumber43> + 20 and
x4_y1>=<#randomnumber46> and
x4_y1<=<#randomnumber46> + 20 and
x4_y2>=<#randomnumber49> and
x4_y2<=<#randomnumber49> + 20 and
x4_y3>=<#randomnumber52> and
x4_y3<=<#randomnumber52> + 20 and
x4_y4>=<#randomnumber55> and
x4_y4<=<#randomnumber55> + 20 and
x4_y5>=<#randomnumber58> and
x4_y5<=<#randomnumber58> + 20 and
x5_y1>=<#randomnumber61> and
x5_y1<=<#randomnumber61> + 20 and
x5_y2>=<#randomnumber64> and
x5_y2<=<#randomnumber64> + 20 and
x5_y3>=<#randomnumber67> and
x5_y3<=<#randomnumber67> + 20 and
x5_y4>=<#randomnumber70> and
x5_y4<=<#randomnumber70> + 20 and
x5_y5>=<#randomnumber73> and
x5_y5<=<#randomnumber73> + 20;

ON MYSQL it's takes very very lot of time (hourS) to return
but on Firebird with same amount of data they return immediatly ... (firebird soo good?)
i have the same probleme on sqlite3... it's very very slow too

THis is simply for my Firebird / MYSQL / SQLite3 benchmark tool

so i definitivelly make a bad point to Mysql against firebird
or i do something wrong (want to be honest in the report) ?

thanks again
stéphane

On 12/9/2010 9:48 PM, Ann W. Harrison wrote:
>
> On 12/9/2010 11:30 AM, Vander Clock Stephane wrote:
>
> >
> > i select the rows only like this
> >
> > select * from ZillionRows where OBJECTID=xxx;
> >
>
> Since you bring it up, let me rant a moment on one of
> my favorite topics.
>
> <rant>
> The *worst* feature in SQL is "select *". It's a holdover
> from an interactive pedagogical language circa 1974 that
> has no place in a programming language. It defeats the
> use of one of the major features of relational databases -
> the separation of logical and physical storage. If you
> realize after you've built your application that you really
> need a Skype handle as well as a phone number, just add it.
> As long as you've used field names in your queries, everything
> old works just the way it did and new queries and updated
> queries can use the Skype handle. But put one Select *
> into the mix and you've got chaos.
>
> The second worst is an insert without a target field list.
> I won't bore you with the third through 400th worst features
> of SQL.
> </rant>
>
> Ann
>
>


[Non-text portions of this message have been removed]