CODEWITHSUNDEEP
phpmysqllaravel
Jay
Jay

Reputation: 443

Speeding Up a Zip Code Distance search with 500k items to search

I have a database with 500k to sometimes over 1 million locations in it.

Users on my website enter their zipcode, and I need to show them locations sorted by distance. This was actually quite fast when I only had 100k locations in my DB, but now it is taking 3+ seconds, even on a very large VPS instance.

Here is my query to use lat/lng and search by distance:

select *,
( 3959 * acos( cos( radians(41.8817767) ) *
cos( radians( lat ) )
* cos( radians( lng ) - radians(-87.6371461)
) + sin( radians(41.8817767) ) *
sin( radians( lat ) ) )
) AS distance from `locations` where `locations`.`deleted_at` is null     having `distance` < '500' order by `distance` asc limit 500

Is there anything obvious here that I can do to speed this up? I can keep upgrading my server but that gets expensive quick.

Upvotes: 1

Views: 73

Answers (1)

drmarvelous
drmarvelous

Reputation: 1681

I'd recommend using elastic search, as it has built in support for GIS/distance calculation.

Add your objects to elastic search, and then provide it with a list of distances and object identifiers to your MySQL table. Once you get a list back from a query (below), simply obtain those objects which were selected from the search.

GET /attractions/restaurant/_search {
  "query": {
    "filtered": {
      "filter": {
        "geo_bounding_box": {
          "type":       "indexed",
          "location": {
            "top_left": {
              "lat":  40,8,
              "lon": -74.0
            },
            "bottom_right": {
              "lat":  40.4,
              "lon": -73.0
            }
          }
        }
      }
    }
  },
  "sort": [
    {
      "_geo_distance": {
        "location": { 
          "lat":  40.715,
          "lon": -73.998
        },
        "order":         "asc",
        "unit":          "km", 
        "distance_type": "plane" 
      }
    }
  ]
}

(Snippet taken from https://www.elastic.co/guide/en/elasticsearch/guide/current/sorting-by-distance.html)

Upvotes: 1

Related Questions