# 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 && 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

• Welcome to Code Review! I changed your title so that it actually describes what you are trying to accomplish (see How to Ask). Please verify that I have not misunderstood your intent. – AlexV Jul 2 at 7:41

1. The formatting is not at all conducive to reading the query. Using indentation would go a long way; breaking the line before rather than after the keyword which introduces a clause (FROM, WHERE, etc.) would also help.
2. totalshops does a count solely for having totalshops > 1. Can that be rewritten as a WHERE EXISTS?
3. The distance calculation is serious overkill. You don't actually need the distance for the query: any strictly monotonic function of it would do. If you denormalise slightly and store Cartesian coordinates in addition to the latitude and longitude then a simple dot product with DESC sorting would work. If you need the distance in the presentation layer, calculate it there.