More efficient way to rewrite this query?

[sfraise]

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

[Old Pedant]

Let's start my making the query readable:

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

Okay... for starters, you may *THINK* you have a series of LEFT JOINs there, but in fact because of your WHERE clause you have converted EVERY ONE OF THEM into an INNER JOIN!

Look here:
http://www.codingforums.com/showthre...192#post818192

You will have to decide if you NEED those to be LEFT JOINs, so that you will then rewrite all the ON conditions, or if INNER JOINs are adequate.

And then you need to use MySQL's EXPLAIN to see what it says it is going to do.

You also have at least one self-contradictory condition in there. To wit:

WHERE ... AND f.memberid = 1593 ...
...
AND f.memberid NOT IN ( 1593, ... )

You have, effectively, said "never select a record from table f".

Finally, I don't see how we can guess how to help you when you don't show the definitions of your two forced indexes.

[sfraise]

Thanks for the reply, I never thought of those as inner joins.
I did notice the contradiction on the f alias however which I'm not sure what the idea was behind that.

I was able to track down exactly where that query was being created, which was in a function to determine the degree of separation between two members. We don't need or utilize that functionality so for the time being I simply commented the query out of the function, out of sight out of mind for now. At least this way if for some reason that functionality is needed down the road I can simply rewrite the query then.

Just from curiosity, I realize joins usually perform better than subqueries, but in this case would subqueries make sense over how this was written?

[Old Pedant]

Don't know the answer to your question, offhand.

I would have to make some experiments. And I strongly suspect that looking carefully at what indexes are defined would be worth the time if you pursue this.

Again, EXPLAIN is your first tool on something like this.

[sfraise]

Thanks again!

[timgolding]

sorry didn't mean to post here