title: MYSQL Geo Search having distance performance
body: <p>I have a mysql select statement for a search on my website which is having performance problems when the site gets really busy. The query below searches for adverts from a table with over 100k records, within 25 miles of the given lat and lon and sorts by distance. The number of miles can differ as it is selected by the user.</p>
  
  <p>The problem is that I think it is slow because it does the calculations for all records in the table rather than ones that are within 25 miles of the lat and lon.  Is it possible to amend this query so that where clause selects only adverts within 25 miles?  Ive read about bounding box's and spatial indexes but im not sure how to apply them to this query, do I need to add a where clause that selects records 25 miles radius of the lat and lon, how do I do this?   </p>
  
  <pre><code>SELECT 
      adverts.*, 
      round(sqrt((((adverts.latitude - '53.410778') * (adverts.latitude - '53.410778')) * 69.1 * 69.1) + ((adverts.longitude - '-2.97784') * (adverts.longitude - '-2.97784') * 53 * 53)), 1) as distance
  FROM 
      adverts
  WHERE 
      (adverts.type_id = '3')
  HAVING 
      DISTANCE &lt; 25
  ORDER BY 
      distance ASC 
  LIMIT 120,10
  </code></pre>
  
  <p>Edit: Updated to include table schema, please note that table is more complicated and so is the query but I have removed the things which aren't necessary for this problem.</p>
  
  <pre><code>CREATE TABLE `adverts` (
  `advert_id` int(10) NOT NULL AUTO_INCREMENT,
  `type_id` tinyint(1) NOT NULL,
  `headline` varchar(50) NOT NULL,
  `description` text NOT NULL,
  `price` int(4) NOT NULL,
  `postcode` varchar(7) NOT NULL,
  `latitude` float NOT NULL,
  `longitude` float NOT NULL,
  PRIMARY KEY (`advert_id`),
  KEY `latlon` (`latitude`,`longitude`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8
  </code></pre>
  
  <p>when i do an explain on the mysql statement the number of rows is set to 67900 which is a lot more than is in a 25 mile radius, also the extra is set to "Using where; Using filesort".</p>
  
  <p>The query takes 0.3 seconds which is really slow, especially when the websites gets lots of requests per second.  </p>