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 && 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 > 1 order by distance ASC LIMIT 1