CountMutualFriends() causes huge lock-up

I've narrowed down the cause of the huge load tied to the database that was causing chain reaction lock-ups of pages and in a short time (seconds) causes the entire site to lock up until the database times out.

The issue is with the CountMutualFriends() function (line 591 in BxBaseProfileView.php).  This function queries the database to find out people who have befriended the account of the profile of the logged in member and it triggers whenever there's a page which calls the function. The performance on this query is abysmal.

I thought I was going to be smart and set myself up as the friend of all migrated accounts and pre-populated the sys_friend_list table.  Totally normal thing, within spec of the database, but I had 37,000 records in there.  Even that should not cause a problem but apparently the query is grossly inefficient and should be re-coded.

try this mod

http://www.boonex.com/unity/extensions/entry/Optimization_Friends_and_Mutual_Friends_Block

Thank you very much, I'll give this one a try.

I didn't see it in the market, seems when searching for free mods, I only get 4 results back.

That did the trick, thank you SO much!  That saved me 2-3 hours, at least, trying to optimize the SQL queries myself.

http://www.boonex.com/trac/dolphin/ticket/1819

JBFB, post some reviews so that we can continue posting free mods

http://www.boonex.com/unity/extensions/entry/Optimization_Friends_and_Mutual_Friends_Block

I'm just a basic member, it won't let me share a review.

Kindly refrain from using the bug reporting forum as an advertising billboard for your friggin mods!!!!!!!!!!!!!

Um... his mod is free & it fixed an actual bug.

Some of the features this mod optimize are already optimized in Dolphin 7 !

Please use official optimization fixe

I don't care if he pays me to use it.  The only people that should be fixing bugs, is the Boonex Dolphin 7 development team.  There are plenty of people around here that offer bug fixing suggestions for Boonex to consider.  I find it a bit distasteful to use every 'bug' you can, as an opportunity to blow your own horn.  I'm pretty sure I'm not alone in that sentiment.

AlexT -- where is it located I cant find it here in the forum? thanks I don't have to do it twice and the existing dolphin 7.0.0 site owner could benefit from it.

@ ExpertX

Have a look there ;-) http://www.boonex.com/trac/dolphin/changeset/13649

JBFB, post some reviews so that we can continue posting free mods

..............you mean UNLIKE the mod listed here - which is $25.

Last I checked, that is NOT free.

I am using Dolphin 7, and the mod he provided fixed the performance issue.

Are you saying that the official fix from Boonex is the same thing?  Or is it even more efficient than what dolphinbugfixing shared?  Sorry, I haven't looked at it yet.  Seems like the query is too over-done to begin with.  The count can be gotten by passing the initial results into a scalar context and there doesn't appear to be a need for a table join.  Well, I'm not an SQL expert but I've done a lot of it for large traffic sites and often times the fastest queries are ones that don't try to do everything in the query itself and allow the code to handle post-processing of the data sets returned except in cases where RAM overhead might be an issue.

I think one of the points Alex is trying to make, is that "Bug Fixes" have no place being listed in the market.  If someone has a suggestion on how to fix something, it should be posted in this forum, or entered into trac, so it can be routed through proper channels.  If people start applying unofficial "Bug Fixes", all it's going to do is create confusion, and possibly compound the issue.   If Boonex fixes the same issue in a different way, and then someone comes along and applies someone else's idea of a fix, you'll end up with a bunch of  Frankenscript.

I have no problem at all with anyone suggesting bug fixes, but placing code modifications in the market, and labeling them "Bug Fixes" is a bad practice.  You people that want to post "Bug Fixes" in the market really need to find some other way to 'gain reputation'.

Its free if you're premium boonex member

LEFT JOIN,RIGHT JOIN or INNER JOIN its a killer, to avoid this used SUB SELECT even in where clause you can used SUB SELECT to acheived performance.

In version 7.0.1 this fixed was NOT included, so its still here for you to download :

http://www.boonex.com/unity/extensions/entry/Optimization_Friends_and_Mutual_Friends_Block_2010_05_01