Skip to content

Instantly share code, notes, and snippets.

@ryantology
Created August 17, 2011 00:38
Show Gist options
  • Save ryantology/1150536 to your computer and use it in GitHub Desktop.
Save ryantology/1150536 to your computer and use it in GitHub Desktop.
MySQL query for closest X records to Y point
# Query to get closest 10 records
SELECT `Idea`.`id`, `Idea`.`title`, `Idea`.`description`, `Idea`.`vote_count`, `Idea`.`fullname`, `Idea`.`location`, `Idea`.`orgname`, `Idea`.`finalist`, `Idea`.`semifinalist`, `Idea`.`winner`, `Idea`.`broker_winner`, `Idea`.`receive_donations`, `User`.`broker`, `User`.`brokerage_id`, `Category`.`id`, `Category`.`file_name`, `User`.`id` FROM `ideas` AS `Idea` LEFT JOIN `users` AS `User` ON (`Idea`.`user_id` = `User`.`id`) LEFT JOIN `categories` AS `Category` ON (`Idea`.`category_id` = `Category`.`id`) WHERE `Idea`.`removed` = 0 AND `Idea`.`approved` = 1 AND `contestyear` = '2010-01-01' AND ((((ROUND(`Idea`.`lat`, 4) != 43.666700) AND (ROUND(`Idea`.`lon`, 4) != -79.416700))) OR (((`Idea`.`lat` = 43.666700) AND (`Idea`.`lon` = -79.416700)))) ORDER BY (6371 * 2 * ATAN2( SQRT( SIN(RADIANS(43.6667 - `Idea`.`lat`)/2) * SIN(RADIANS(43.6667 - `Idea`.`lat`)/2) + SIN(RADIANS(-79.4167 - `Idea`.`lon`)/2) * SIN(RADIANS(-79.4167 - `Idea`.`lon`)/2) * COS(RADIANS(43.6667)) * COS(RADIANS(-79.4167)) ), SQRT(1 - ( SIN(RADIANS(43.6667 - `Idea`.`lat`)/2) * SIN(RADIANS(43.6667 - `Idea`.`lat`)/2) + SIN(RADIANS(-79.4167 - `Idea`.`lon`)/2) * SIN(RADIANS(-79.4167 - `Idea`.`lon`)/2) * COS(RADIANS(43.6667)) * COS(RADIANS(-79.4167)) )) ) * 1) ASC LIMIT 10;
# Explain:
+----+-------------+----------+--------+------------------------------+----------+---------+---------------------------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+--------+------------------------------+----------+---------+---------------------------+------+-----------------------------+
| 1 | SIMPLE | Idea | ref | lat,lon,approved,contestyear | approved | 1 | const | 3794 | Using where; Using filesort |
| 1 | SIMPLE | User | eq_ref | PRIMARY | PRIMARY | 4 | aviva_af.Idea.user_id | 1 | |
| 1 | SIMPLE | Category | eq_ref | PRIMARY | PRIMARY | 4 | aviva_af.Idea.category_id | 1 | |
+----+-------------+----------+--------+------------------------------+----------+---------+---------------------------+------+-----------------------------+
# Show Create Table
CREATE TABLE `ideas` ( `id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`title` varchar(64) NOT NULL,
`description` text NOT NULL,
`location` varchar(255) NOT NULL,
`glocation` varchar(255) NOT NULL,
`lat` float NOT NULL DEFAULT '0',
`lon` float NOT NULL DEFAULT '0',
`category_id` int(11) NOT NULL,
`created` datetime NOT NULL,
`contestyear` date NOT NULL,
PRIMARY KEY (`id`),
KEY `lat` (`lat`),
KEY `lon` (`lon`),
KEY `user_id` (`user_id`),
KEY `finished` (`finished`),
KEY `approved` (`approved`),
KEY `reviewed` (`reviewed`),
KEY `vote_count` (`vote_count`),
KEY `contestyear` (`contestyear`)
) ENGINE=MyISAM AUTO_INCREMENT=10894 DEFAULT CHARSET=utf8
# DO NOT HATE ON MyISAM - It is an ops issue. I can't fix now.
@morgo
Copy link

morgo commented Aug 17, 2011

Even if it says filesort, it doesn't mean it sorts on disk in a file - explain doesn't yet know where the sort happens.

You can literally sort millions of rows/second in a single core (assuming they are mostly pre-sorted, which a lot of data actually tends to be). It's important to look at the 'Rows' count to keep it in context.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment