0
\$\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\$
  • \$\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 at 7:41
1
\$\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.