Re: mySQL vs Interbase

[Jeremy Zawodny]

On Thu, Jan 10, 2002 at 10:25:55AM -0600, Mike Grover wrote:
>
> I have a database in Firebird(Interbase) that has 106 million
> records at 37.4 gig.
> 
> I imported the database into mySQL and built the same indexes.
> 
> with a query like this:
> 
> select * from experian.experian where latitude >= '038631928' and
> latitude <= '038638092' and longitude >= '096671646' and longitude <=
> '096680757';
> 
> Interbase takes 10 seconds to return 70 records, but mySQL takes 18 seconds 
> to return the same record count.
> 
> Is there any way to speed up mySQL?

Probably, yes.

> my Index is:
> 
> ALTER TABLE EXPERIAN.EXPERIAN ADD INDEX latlong (latitude,longitude);
> 
> Both fields are 9 chars long.
> 
> "explain" says it is using the latlong index with a key length of 20.
> 
> Is this the best mySQL will do? Is there a better sql statement I
> can use?

Is that the speed you see on every query or only the first query?  How
large is your key buffer?

Jeremy
-- 
Jeremy D. Zawodny, <jzawodn@stripped>
Technical Yahoo - Yahoo Finance
Desk: [phone]   Fax: [phone]   Cell: [phone]

MySQL 3.23.41-max: up 7 days, processed 193,814,752 queries (288/sec. avg)