2
\$\begingroup\$

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
\$\endgroup\$
1
  • \$\begingroup\$ 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. \$\endgroup\$
    – AlexV
    Jul 2 '19 at 7:41
4
\$\begingroup\$

I think there's three big things you might be able to do to improve the performance of this.

  1. 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.

  2. 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.

  3. 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.

\$\endgroup\$
2
\$\begingroup\$

MySQL surely has some tools to help you profile the query, and you should try using those to at least identify the bottleneck. But there are three things which jump out at me.

  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.
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.