MySQL query for closest X records to Y point

gistfile1.sql

# 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.

I never really measure the performance difference between individual and composite indexes. With 10k records it's going to be trivial I'm sure.

I would love to see a way to get rid of the filesort but I'll probably have to wait for 5.6

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.