Querying nearest city with a given shop using latitude and longitude is slow I am using the following query to search for the nearest city to my location from the cities table using latitude and longitude. The query also checks whether the nearest cities to my location have more than 1 shops of a given category. Only then a city is selected.

The query works fine but it takes more than 5 seconds to execute. Is there any way this query can be optimised to consume less time.

SELECT id,city,city_url, 
(SELECT count(shop) FROM shops JOIN         
shop_categories ON shop_categories.shop_id = shops.id WHERE 
city_id=cities.id &amp;&amp; shop_categories.category_id = :catId LIMIT 1) as 
totalshops, 
(6371 * 2 * ASIN(SQRT( POWER(SIN(( :latitude - latitude) *  
pi()/180 / 2), 2) +COS( :latitude * pi()/180) * COS( :latitude * 
pi()/180) * POWER(SIN(( :longitude - longitude) * pi()/180 / 2), 2) ))) 
as distance
from cities
having totalshops &gt; 1
order by distance ASC
LIMIT 1
I think there's three big things you might be able to do to improve the performance of this.


Write your query such that it doesn't need to execute the subquery for each line and/or have a HAVING clause by using an inner join which will automatically exclude those with no match.
Use ST_Distance_Sphere(POINT(:longitude, :latitude), POINT(cities.longitude, cities.latitude) to get the distance using a built in function that'll probably be faster than anything you can calculate outside of one.
If you're looking for the singular closest entry it may be worth using half of the maximum distance between two points as a cut off and excluding anything by default where the long or lat is outside of the given range and would thus never be the closest entry.
For example if the largest distance between two cities is 1,600 km you know for sure that no city outside of 800 km in any direction can ever be the closest; At 70N about the north end of Canada that equates to ~21 points of longitude anything outside of your passed in longitude +- 22 can be immediately ignored. The same rule can be applied to latitude to ignore everything outside of +/- 7.

You'll still have to check everything in those ranges, but it'll be very quick to run and if it reduces the amount of slow work required to be run it's worth it.



SELECT
    cities.id,
    cities.city,
    cities.city_url,
    COUNT(DISTINCT shops.id) AS totalshops,
    ST_Distance_Sphere(POINT(:longitude, :latitude), POINT(cities.longitude, cities.latitude) AS distance
FROM cities
JOIN shops
    ON
    shops.city_id = cities.id
JOIN shop_categories
    ON
    shop_categories.shop_id = shops.id
WHERE
    shop_categories.category_id = :catId
    AND
    cities.longitude BETWEEN :longitude - :max_long_distance AND :longitude + :max_long_distance
    AND
    cities.latitude BETWEEN :latitude - :max_lat_distance AND :latitude + :max_lat_distance
GROUP BY
    cities.id
ORDER BY
    distance ASC
LIMIT 1
;


PS: I assumed for the GROUP BY clause you're not running with the strict grouping mode enabled. If that's not the case you'll need to add the other columns from cities that are references like the cities.city, cities.city_url, cities.longitude and cities.latitude.