首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >MySQL左连接返回花费的时间太长

问MySQL左连接返回花费的时间太长
EN

Stack Overflow用户
提问于 2016-02-26 18:38:33
回答 1查看 343关注 0票数 2

我有一个问题,因为我有三个表tbldispensaries,tblStates,tblCountries。我将国家和州的id存储在药剂表中,所有与国家和州相关的详细信息都分别在tblCountries和tblStates中。

我正在编写一个查询,以查找半径为500的附近药房,以便在Google Maps上显示标记,该查询很好,但当我在查询中使用left join时,响应时间太长。我尝试过使用索引,但这对它也没有影响。所以请建议我克服这个问题。下面是我正在使用的查询:-

代码语言:javascript
复制
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
EN

回答 1

Stack Overflow用户

发布于 2016-03-19 23:09:10

这可能不是一个答案,但它是两个大的评论。此外,我没有数据,所以我不能测试,但它在这里。

代码语言:javascript
复制
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会更快。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/35649477

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档