I have a long query that is zapping resources and need to rewrite it. The obvious problem with it is the use of "not in" in the where clause. My initial thought is to rewrite it doing away with all of the self joins and doing a "not exists" on a subquery. Any thoughts on doing that, or a maybe a more efficient idea than that one?
Here's the query:
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