Optimising SQL distance query
- by Alex
I'm running an MySQL query that returns results based on location. However I have noticed recently that its really slowing down my PHP app. I used CodeIgniter and the profiler shows the query taking 4.2seconds. The geoname table has 500,000 rows. I have some indexes on the key columns, how else can speed up this query?
Here is my SQL:
SELECT `products`.`product_name`, `geoname`.`geonameid`, `geoname`.`latitude`, `geoname`.`longitude`, `products`.`product_id`, AVG(ratings.vote) as rating, count(comments.comment_id) as total_comments, (6371 * acos(cos(radians(38.7666667))
* cos(radians(geoname.latitude))
* cos(radians(geoname.longitude) - radians(-3.3833333))
+ sin(radians(38.7666667))
* sin(radians(geoname.latitude)))) AS distance
FROM (`foods`)
JOIN `geoname` ON `geoname`.`geonameid` = `products`.`geoname_id`
LEFT JOIN `ratings` ON `ratings`.`var_id` = `products`.`product_id`
LEFT JOIN `comments` ON `comments`.`var_id` = `products `.`product_id`
WHERE `products`.`product_id` != 82
GROUP BY `products`.`product_id`
HAVING `distance` < 99
ORDER BY `distance`
LIMIT 10
