I just wanted to mention I've been noticing a long query crashing mysql lately on our site stemming out of the connections tab. The query I'm getting is...
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 = 1365 AND a.accepted=1 AND a.pending=0 AND f.memberid = 1365
AND b.memberid NOT IN ( 1365,a.memberid)
AND c.memberid NOT IN ( 1365,a.memberid,b.memberid)
AND d.memberid NOT IN ( 1365,a.memberid,b.memberid,c.memberid)
AND e.memberid NOT IN ( 1365,a.memberid,b.memberid,c.memberid,d.memberid)
AND f.memberid NOT IN ( 1365,a.memberid,b.memberid,c.memberid,d.memberid,e.memberid) LIMIT 0, 1
I'm running an older version of cb 1.2.3 on Joomla 1.5 so maybe this has been addressed in the newer versions.
I'm guessing at first glance that the NOT IN part of the query is what's killing the performance, plus there's a lot of joins that I don't know are really needed here. I'm going to replace it with my own plugin so I can rewrite the queries from scratch and implement an ajax pagination instead of using the standard old pagination this uses so it's not a big deal, just thought I'd share what I see in case it's useful to others. I doubt this is much of an issue for small membership sites but in this case we have a couple thousand members (which is still pretty small) but we have a couple of admin accounts that automatically get connected to all members when they join which means those accounts have a couple thousand friends.
krileon
