0

i have a problem with this query:

  SELECT city,6371 * ACos( Cos(RADIANS(Lat)) * Cos(RADIANS(51.166698)) * Cos(RADIANS(-1.7833) -   RADIANS(Lng)) + Sin(RADIANS(Lat)) * Sin(RADIANS(51.166698)) ) AS Distance 
  FROM GeoPC 
  GROUP BY city ORDER BY Distance LIMIT 20

The query needs about 30 seconds. There are about 1.7 million rows in the database and the group by and the order by is too heavy for the database. Any idea how i can fix it?

Suresh Kamrushi
  • 15,627
  • 13
  • 75
  • 90
user3091381
  • 1

2 Answers2

0

Have you tried moving the Distance parameter to a calculated field in the GeoPC table? On the face of it I can't see it making a massive difference but it's something quick to try.

Nick
  • 685
  • 12
  • 27
0

Your using the Haversine function to calculate the distance between two points in the WGS84 geodetic coordinate system (e.g., lat/lng). MySQL supports geospatial types and functions which are much higher performance. You will need to replace the fields Lat, Lng (which I assume are of type decimal) with a single field of type POINT(). There are methods for walking through your table to do this. Once done, you can use MySQL's geospatial methods to calculate distance between two points. Here's a recent blog on the subject:

http://www.mysqlperformanceblog.com/2013/10/21/using-the-new-spatial-functions-in-mysql-5-6-for-geo-enabled-applications/

Here's the MySQL online documentation on geospatial types: http://dev.mysql.com/doc/refman/5.0/en/mysql-spatial-datatypes.html

Andrew - OpenGeoCode
  • 2,299
  • 18
  • 15
  • Thanks a lot. I tried it but it is also slow. The problem is the "Order By Distance" Statement, this makes my query very slow. Without "Order By Distance" it's fast. – user3091381 Dec 12 '13 at 08:51
  • I'm not sure how good these answers are, but here's a previous post on the same thing with several answers: http://stackoverflow.com/questions/16276752/mysql-query-slow-when-using-order-by-with-function-to-calculate-distance-between – Andrew - OpenGeoCode Dec 13 '13 at 21:27