10

I'm creating an MySQL call using PHP, I'm calculating distance using the haversine forumula:

SELECT name, id,
   (6371 * acos(cos(radians(' . $lat . ')) 
   * cos(radians(geoname.latitude)) 
   * cos(radians(geoname.longitude) - radians(' . $lon . ')) 
   + sin(radians(' . $lat . ')) 
   * sin(radians(geoname.latitude)))) AS distance

My question is; is it best to do all this calculation in SQL? This query searches a table with around 1000 records. Would it be more efficient to do some of the math in PHP rather than SQL? Is there a better way to optimise this query?

Alex
  • 6,497
  • 11
  • 47
  • 58

2 Answers2

4

You're using the Spherical Law of Cosines formula, not the Haversine Formula (which is slightly slower).

Doing the math in MySQL will probably be much faster, I advise you to read this question I asked a while back, as for speed optimizations you should definitely read the excellent Geo Proximity Search with MySQL paper, pay special attention to pages 8-14 and 19.

Community
  • 1
  • 1
Alix Axel
  • 151,645
  • 95
  • 393
  • 500
2

For a super fast MySQL indexer, Check out Sphinx. It is extremely fast for finding what you need in your MySQL database, and does geo/distance calculations automatically.

Tutorial: Geo/Spatial Search Using Sphinx Search and PHP

philfreo
  • 41,941
  • 26
  • 128
  • 141