A few seconds too me is too much lag. OSSN can’t be very scalable then under the current schema structure. I’m a professional data architect and data engineer and in my day job, I performance tune MSSQL server processes quite a lot. I have not worked with MySQL much but the principal s must be the same I would think.

I don't think you need a separate table unless your network has several thousand users. It depends a lot on what your host's database server can handle, though. Even with knowing server specs, it's difficult to get an idea of actual performance unless you do some testing. It probably won't create more than a few seconds of lag time unless you have several thousand users.

It seems that this solution is better of having its own indexed table. As friends are made or deleted, the table is upserted. Then the sql logic against the table, where the joins on on the indexes, would compare the friend lists of two people and count the intersections

Also, that table would have only a handful of columns; at least one for the user id and one for the friend user Id, a one to many relationship. Those two would be the primary key which would be an automatic index. Using a dedicated table takes to pressure of any core table.

Also, there needs to be an option in such a component to build or refresh this table at will. On install, the logic would need to run. But the admin should be able to refresh any time they want to.

Do you think it would be less intensive to just list the mutual friends on the profile page and would not create any noticeable lag?

the object I was referring to in my last post is that returned by ossn_logged_in_user() - it may have friend info in there, I don't remember offhand, it should be easy enough to check

there's also an object passed b/w pages that might be useful to check, it's called either $user or $Ossn. You should be able to find it in the source without much trouble. I believe that contains all of the currently logged in user's friends, but I may be mistaken.

the table to query would be ossn_relationships - what makes this difficult is that a friend relation is defined as having 2 rows in that table, so for example if user A is friends with user B, there are 2 entries - the first created (for example) when A sends a friendship request to B; the second is created when B accepts the request.

so lets assume A and B are now friends. When A views B's profile, you want to show up to 10 users that are friends with both A and B. I'm pretty sure the restriction of up to 10 isn't going to make much, if any difference. You're still going to need to query ossn_relationships in the same way.

If we are going by the People list we would only calculate the number for 10 people at a time. Do you know if the underlying tables have gotten indexed to handle such a query?

one potential issue I see is that it would be rather database intensive, unless there's a simpler way to write a query for it that i'm missing... if your network has, for example, 10k users, that query for mutual friends is likely to slow things down considerably. if we're just talking about a relatively small network though, it shouldn't be an issue.

@William Shoap ... he's doing custom ($$$) work. I have him working on something for me. I will likely have him do some mutual friend stuff later down the line for me, but his e-mail is on his profile page.