MySQL - Ordered GROUP BY Issue Optimization

Question

I have a table called stores which contains store information and more importantly location coordinates of stores of various merchants. A merchant can have multiple stores at various locations. I need a listing of stores ordered by distance (from a particular location) with only the closest store of each merchant showing in the list.

The following query works but I believe its sub-optimal. Is there a way to revise or improve this query?

SELECT 
    `Store`.`id`, 
    `Merchants`.`id`, 
    `Store`.`area_id`, 
    `Store`.`url`, 
    ( 6371 * acos( cos( radians(18.973212066666665) ) * cos( radians( Store.latitude ) ) * cos( radians( Store.longitude ) - radians(72.8140959) ) + sin( radians(18.973212066666665) ) * sin( radians( Store.latitude ) ) ) ) AS distance 
FROM 
    `stores` AS `Store` 
INNER JOIN 
    (SELECT DISTINCT id,( 6371 * acos( cos( radians(18.973212066666665) ) * cos( radians( Store.latitude ) ) * cos( radians( Store.longitude ) - radians(72.8140959) ) + sin( radians(18.973212066666665) ) * sin( radians( Store.latitude ) ) ) ) AS distance FROM stores as Store WHERE Store.active=1 AND Store.parent_id=0 AND (( 6371 * acos( cos( radians(18.973212066666665) ) * cos( radians( Store.latitude ) ) * cos( radians( Store.longitude ) - radians(72.8140959) ) + sin( radians(18.973212066666665) ) * sin( radians( Store.latitude ) ) ) ) < 50) GROUP BY Store.id ORDER BY distance) AS `St` ON (`St`.`id` = `Store`.`id`) 
INNER JOIN 
    merchants AS `Merchants` ON (`Store`.`merchant_id` = `Merchants`.`id`) 
WHERE 
    (( 6371 * acos( cos( radians(18.973212066666665) ) * cos( radians( `Store`.`latitude` ) ) * cos( radians( `Store`.`longitude` ) - radians(72.8140959) ) + sin( radians(18.973212066666665) ) * sin( radians( `Store`.`latitude` ) ) ) ) < 50) AND 
        `Store`.`parent_id` = 0 AND 
        `Store`.`active` = 1  
GROUP BY 
    `Merchants`.`id`  
ORDER BY 
`distance` ASC  
LIMIT 10

I am using the following formula to calculate distance ( 6371 * acos( cos( radians(LATITUDE) ) * cos( radians( Store.latitude ) ) * cos( radians( Store.longitude ) - radians(LONGITUDE) ) + sin( radians(LATITUDE) ) * sin( radians( Store.latitude ) ) ) )

Bulbasaur Bulbasaur · Accepted Answer · 2012-07-31T18:18:16

Looks like you don't need the merchant table at all. You are using it only for merchant_id which you have in the store table anyways. So you can avoid this join.

If I understand what you need correctly, you can try this query:

    select id, merchant_id, area_id, url, 
min(distance function you are using) as distance 
from stores group by merchant id, order by distance

I did not actually run it, but the idea should work.

-Mansi

MySQL - Ordered GROUP BY Issue Optimization

1 Answers