List:General Discussion« Previous MessageNext Message »
From:Tyler G. Nally Date:January 10 2002 5:55pm
Subject:RE: mySQL vs Interbase
View as plain text  
Yeah, any lookups based on char based columns are slower
than integer queries any day.  I'm assuming it's a char
column because you're searching with apostrophe's '00000'.

Would a "between" statement in the where clause help?

  select * from experian.experian
   where latitude between '038631928' and '038638092'
     and longitude between '096671646' and '096680757'


> -----Original Message-----
> From: Dan Nelson [mailto:dnelson@stripped]
> Sent: Thursday, January 10, 2002 12:09 PM
> To: Mike Grover
> Cc: mysql@stripped
> Subject: Re: mySQL vs Interbase
> 
> 
> In the last episode (Jan 10), Mike Grover said:
> > 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.
> > 
> > my Index is:
> > ALTER TABLE EXPERIAN.EXPERIAN ADD INDEX latlong 
> (latitude,longitude);
> > 
> > "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?
> 
> That's about as efficient as you can get.  You can try raising your
> mysqld key_buffer_size to allow more of the index to be cached, or
> maybe use INTEGER types for lat and long, which will bring your key
> down to 8 bytes total instead of 20 (cutting your index size by more
> than 50%).
> 
> -- 
> 	Dan Nelson
> 	dnelson@stripped
> 
> ---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)
> 
> To request this thread, e-mail <mysql-thread95997@stripped>
> To unsubscribe, e-mail 
> <mysql-unsubscribe-tnally=legion.org@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 

Thread
RE: mySQL vs InterbaseMike Grover10 Jan
  • Re: mySQL vs InterbaseDan Nelson10 Jan
  • Re: mySQL vs InterbaseJeremy Zawodny10 Jan
RE: mySQL vs InterbaseTyler G. Nally10 Jan