0

My original question is here: Slow location search

I've have spent a fair bit of time on the code to get a better understand of what exactly the code is / should be doing. The code: 

SELECT (fieldnames),  
( 3959 * acos( cos( radians( $lat ) ) * cos( radians( `lat` ) ) * cos( radians( `lng` ) - radians( $lng ) ) + sin( radians( $lat ) ) * sin( radians( `lat` ) ) ) )  AS distance
FROM details
inner JOIN postcodes ON details.postcode = postcodes.outcode AND payment='y' AND expired='n' HAVING " . implode(" AND ", $searches)."  ORDER BY details.asking_price

The postcode is being taken from the users input calculating the long and lat and checking the distance from the postcodes table (postcode, long, lat).

Implode is checking if there are any other variable that need to be added to the search.

I have read many different posts but I cant find any that tell me why my search is taking over 10 seconds to perform.

It is something to do with how the distance is being calculated as if I search without distance, the time taken isn't a problem.

Its taking roughly 7/8 seconds to perform the search, down from about 15 seconds from the other post. I've thought about limiting the amount of results just to what are being displayed on that page (20).

Community
  • 1
  • 1
Matt M
  • 43
  • 6
  • MySQL geospatial support is not optimal and it performs badly.. consider using other options that said, do you have spatial indexes? – Sam Jan 23 '14 at 19:40
  • If `$lat` and `$long` are being stored as variables, then pre-calculate things like `cos(radians($lat))`. – Gordon Linoff Jan 23 '14 at 19:41
  • Duplicate of [Geo-Search (Distance) in PHP/MySQL (Performance)](http://stackoverflow.com/questions/5236921/geo-search-distance-in-php-mysql-performance) – Mark Baker Jan 23 '14 at 19:48
  • The database and tables do have spatial indexes, but the columns are currently stored as varchar (not my doing) – Matt M Jan 23 '14 at 20:30

0 Answers0