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
