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.
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.
COUNT(DISTINCT shops.id) AS totalshops,
ST_Distance_Sphere(POINT(:longitude, :latitude), POINT(cities.longitude, cities.latitude) AS distance
shops.city_id = cities.id
shop_categories.shop_id = shops.id
shop_categories.category_id = :catId
cities.longitude BETWEEN :longitude - :max_long_distance AND :longitude + :max_long_distance
cities.latitude BETWEEN :latitude - :max_lat_distance AND :latitude + :max_lat_distance
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