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:[EMAIL PROTECTED]] > Sent: Thursday, January 10, 2002 12:09 PM > To: Mike Grover > Cc: [EMAIL PROTECTED] > 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 > [EMAIL PROTECTED] > > --------------------------------------------------------------------- > 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 <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php