我有一个问题,因为我有三个表tbldispensaries,tblStates,tblCountries。我将国家和州的id存储在药剂表中,所有与国家和州相关的详细信息都分别在tblCountries和tblStates中。
我正在编写一个查询,以查找半径为500的附近药房,以便在Google Maps上显示标记,该查询很好,但当我在查询中使用left join时,响应时间太长。我尝试过使用索引,但这对它也没有影响。所以请建议我克服这个问题。下面是我正在使用的查询:-
SELECT
d.id AS disp,
d.id AS the_id,
d. NAME,
d.address AS addr,
d.city AS city_name,
c.country_name AS cntry_name,
s.state_name AS st_name,
d.zip AS zipcode,
d.latitude,
d.longitude,
(
6371 * ACOS(
COS(RADIANS(33.6119)) * COS(RADIANS(latitude)) * COS(
RADIANS(longitude) - RADIANS(- 111.8906)
) + SIN(RADIANS(33.6119)) * SIN(RADIANS(latitude))
)
) AS distance
FROM
`tblDispensaries` d
LEFT JOIN tblCountries c ON (c.country_id = d.country)
LEFT JOIN tblStates s ON (s.state_id = d.state)
WHERE
d.id IS NOT NULL
AND d. STATUS = 1
HAVING distance < 500
LIMIT 0,60发布于 2016-03-19 23:09:10
这可能不是一个答案,但它是两个大的评论。此外,我没有数据,所以我不能测试,但它在这里。
SELECT
Dispensaries.*,
c.country_name AS cntry_name,
s.state_name AS st_name
FROM (
SELECT
d.id AS disp,
d.id AS the_id,
d. NAME,
d.address AS addr,
d.city AS city_name,
d.zip AS zipcode,
d.latitude,
d.longitude,
(
6371 * ACOS(
COS(RADIANS(33.6119)) * COS(RADIANS(latitude)) * COS(
RADIANS(longitude) - RADIANS(- 111.8906)
) + SIN(RADIANS(33.6119)) * SIN(RADIANS(latitude))
)
) AS distance
FROM
`tblDispensaries` d
WHERE
d.id IS NOT NULL
AND d. STATUS = 1
HAVING distance < 500
LIMIT 0,60
) as Dispensaries
LEFT JOIN tblCountries c ON (c.country_id = Dispensaries.country)
LEFT JOIN tblStates s ON (s.state_id = Dispensaries.state)我认为获取结果并使用JOIN会更快。
https://stackoverflow.com/questions/35649477
复制相似问题