首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >编写此mysql select查询的更好方法

问编写此mysql select查询的更好方法
EN

Stack Overflow用户
提问于 2012-12-06 03:01:36
回答 1查看 91关注 0票数 0

我有一个耗费大量资源的长查询,需要重写它。它的明显问题是在where子句中使用"not in“。我最初的想法是重写它,去掉所有的自连接,并对子查询执行"not exists“。有没有想过这样做,或者是一个比那个更有效的想法?

下面是查询:

代码语言:javascript
复制
SELECT a.referenceid,
       a.memberid AS d1,
       b.memberid AS d2,
       c.memberid AS d3,
       d.memberid AS d4,
       e.memberid AS d5,
       f.memberid AS d6
FROM   jos_comprofiler_members AS a FORCE INDEX (aprm)
       LEFT JOIN jos_comprofiler_members AS b FORCE INDEX (aprm)
              ON a.memberid = b.referenceid
                 AND b.accepted = 1
                 AND b.pending = 0
       LEFT JOIN jos_comprofiler_members AS c FORCE INDEX (aprm)
              ON b.memberid = c.referenceid
                 AND c.accepted = 1
                 AND c.pending = 0
       LEFT JOIN jos_comprofiler_members AS d FORCE INDEX (pamr)
              ON c.memberid = d.referenceid
                 AND d.accepted = 1
                 AND d.pending = 0
       LEFT JOIN jos_comprofiler_members AS e FORCE INDEX (pamr)
              ON d.memberid = e.referenceid
                 AND e.accepted = 1
                 AND e.pending = 0
       LEFT JOIN jos_comprofiler_members AS f FORCE INDEX (pamr)
              ON e.memberid = f.referenceid
                 AND f.accepted = 1
                 AND f.pending = 0
WHERE  a.referenceid = 1593
       AND a.accepted = 1
       AND a.pending = 0
       AND f.memberid = 1593
       AND b.memberid NOT IN ( 1593, a.memberid )
       AND c.memberid NOT IN ( 1593, a.memberid, b.memberid )
       AND d.memberid NOT IN ( 1593, a.memberid, b.memberid, c.memberid )
       AND e.memberid NOT IN (
           1593, a.memberid, b.memberid, c.memberid, d.memberid )
       AND f.memberid NOT IN ( 1593, a.memberid, b.memberid, c.memberid,
                               d.memberid, e.memberid )
LIMIT  0, 1  
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2012-12-06 04:23:57

您的查询将查找距离为6的1593内的所有成员。反问句:这与六度分离有关吗?

通常,在SQL中查找此类列表需要递归查询或某些非SELECT结构(循环、游标)。当列表由变量成员组成时,我不确定MySQL如何处理not in。但是,我怀疑它是在为每个对象执行某种嵌套循环连接。

建议:将not in语句移到on子句。例如:

代码语言:javascript
复制
   LEFT JOIN jos_comprofiler_members AS c FORCE INDEX (aprm)
          ON b.memberid = c.referenceid
             AND c.accepted = 1
             AND c.pending = 0
             AND (c.memberId <> b.MemberId an c.MemberId <> a.MemberId and c.MemberId <> 1593)

这可能会改善情况。或者让他们更糟。但这只是一个想法。

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

https://stackoverflow.com/questions/13730517

复制
相关文章

相似问题

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