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
