I have table with 2 million rows of geographic points (latitude, longitude). Given a location -- say, 52º, -113.9º -- what's the fastest way to query the 10 closest points (records) from that table? Currently, I'm using a simple two-column index to speed up queries:
CREATE TABLE `places` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `latitude` decimal(10,8) NOT NULL, `longitude` decimal(12,8) NOT NULL PRIMARY KEY (`id`), KEY `latlng` (`latitude`,`longitude`) ) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; My current query is fairly quick: SELECT SQL_NO_CACHE * FROM places WHERE latitude BETWEEN 51.98228037384 AND 52.033153677 AND longitude BETWEEN -113.94770681881 AND -113.86685484296; But I wonder a couple things: 1. Would MySQL's [seemingly anemic] spatial extensions would speed things up if I added a column of type POINT (and a corresponding spatial INDEX)? CREATE TABLE `places` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `latitude` decimal(10,8) NOT NULL, `longitude` decimal(12,8) NOT NULL, `coordinates` point NOT NULL, PRIMARY KEY (`id`), KEY `latlng` (`latitude`,`longitude`), KEY `coord` (`coordinates`(25)) ) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; 2. How would I write the query? ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
