Optimizing Friend List + Friends of Friends count query

[asantos]

Im optimizing a social network service. The dashboard shows a list of your friends and their friends' count.

Since a lot of users have more than 20 friends, right now this is whats happening:

1) Loop through all your friends.
2) On each loop, it commits another query that gets the count of friends.

So, if I (user id 1) have 20 friends, there will be 21 sql queries... something like:

SELECT id_friend FROM friends WHERE id_user=1

SELECT COUNT(id_friend) AS q FROM friends WHERE id_user=X
where X is the current loop of the previous' query's id_friend.

Is there any way to join them together? (having 1 query instead of 21).

[asantos]

I came up with:

SELECT t1.id_friend,COUNT(t2.id_friend) AS q FROM friend AS t1,friend AS t2 WHERE t1.id_user=2 AND t2.id_user=t1.id_friend GROUP BY t1.id_friend

Still... does someone think that this could be optimized?

[arlo]

I think this might help.

SELECT id_friend, COUNT(SELECT 1 FROM friends WHERE id_user=f.id_user) as q
FROM friends as f
WHERE id_user=1

Above query will list all friends (of user 1) along with an extra column that says how many friends they have. Let me know if it helps.

[coolclu3]

A good way i think is to have a 'friends_count' field in the table. Each up a new connection is made, update that field. That way, the "insert new connection" will be slow, but information retrieval is faster, much faster