0

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
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Lovejeet
  • 9
  • 1
  • 2
    I'm voting to close this question as off-topic because it belongs to [Code Review Stack Exchange Site](https://codereview.stackexchange.com/) – Alive to die - Anant Jul 02 '19 at 05:53
  • I'm not sure whether it would help, but could try doing some of the distance calculations in PHP. See [Use string functions and arithmetic calculations in SQL query or by programming language?](https://dba.stackexchange.com/a/56743) and [Doing calculations in MySQL vs PHP](https://stackoverflow.com/a/6449162/924299) and [What are the pros and cons of performing calculations in sql vs. in your application](https://stackoverflow.com/a/7510143/924299). – showdev Jul 02 '19 at 05:58
  • What are you trying to achieve with `SELECT count(shop) FROM shops...`? – Nigel Ren Jul 02 '19 at 06:01
  • @NigelRen that query selects the total number of shops for a given category in a city. – Lovejeet Jul 02 '19 at 06:06
  • Can I suggest trying the statement without that code and see if that makes a difference, if so then you can always retrieve that information as a second SQL once you have selected the nearest city. – Nigel Ren Jul 02 '19 at 06:09
  • 1
    What if you just select and then calculate `(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) ))) ` in your php script instead of mysql ? – MorganFreeFarm Jul 02 '19 at 06:11
  • @MorganFreeFarm that query selects the nearest city by taking my latitude and longitude and comparing them against the cities table to find the nearest city. Can you give me some references to do that in php. – Lovejeet Jul 02 '19 at 06:24
  • @NigelRen I guess thats an option. I will try and let you know how it works out. – Lovejeet Jul 02 '19 at 06:25

0 Answers0