Hello,

First i just done a benchmark tool to permit to run the same query on a mysql Server or on a Firebird Server and get the execution time! if someone is interessting : http://www.sourceforge.net/projects/alcinoe/ in the demo directory AlSQLBenchmark

ok, now my probleme. i ask previously about perf result on a simple
select :

select
First 1
ID
from
geo_place
where
ID like 'RU%'

and the answer was that it's normal that this query is slow ! soo i start to build a tool to see if it's the same on MYSQL server (for exemple). below the result :

Table GEO_PLACE
450 000 Reccords
200 000 reccords with ID starting with RU
ID VARCHAR 100
RANK INTEGER
Index on ID (primary key)
Index on RANK
Index on ID, RANK
Index on RANK, ID

***********************
***********************

select
First 1
ID
from
geo_place
where
ID = 'RU'

Firebird average time take: 0 ms
MySql average time take: 0 ms

everything is ok

***********************
***********************

select
First 1
ID
from
geo_place
where
ID like 'RU%'

Firebird average time take: 50 ms
MySql average time take: 0 ms

this was my first problem: understand why this simple like
take so much time ! it's not normal that it's take so much
time as you can see on other SGBD (mysql is not the best) it
very more faster !

***********************
***********************

select
First 1
ID
from
geo_place
where
ID like 'RU%'
order by rank desc

Firebird average time take: 50 ms
MySql average time take: 2000 ms

yes, here firbird is not so bad, but i m
still thinging if their is a way to accelerate
this query ?

**********************
**********************

select
First 1
ID,
rank
from
geo_place
where
ID like 'RU%'
and rank > 0

Firebird average time take: 50 ms
MySql average time take: 70 ms

note that if i force mysql to use the
good index the result is given in 0ms !

********************
********************

select
First 1
ID,
rank
from
geo_place
where
ID like 'RU%'
and rank > 1000

Firebird average time take: 50 ms
MySql average time take: 0 ms


thanks by advance for your comments

--
stephane

http://www.arkadia.com/fra/
http://www.arkadia.com/rus/
http://www.arkadia.com/usa/