我有一个耗费大量资源的长查询,需要重写它。它的明显问题是在where子句中使用"not in“。我最初的想法是重写它,去掉所有的自连接,并对子查询执行"not exists“。有没有想过这样做,或者是一个比那个更有效的想法?
下面是查询:
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 发布于 2012-12-06 04:23:57
您的查询将查找距离为6的1593内的所有成员。反问句:这与六度分离有关吗?
通常,在SQL中查找此类列表需要递归查询或某些非SELECT结构(循环、游标)。当列表由变量成员组成时,我不确定MySQL如何处理not in。但是,我怀疑它是在为每个对象执行某种嵌套循环连接。
建议:将not in语句移到on子句。例如:
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)这可能会改善情况。或者让他们更糟。但这只是一个想法。
https://stackoverflow.com/questions/13730517
复制相似问题