efficient way to query for count totals

Question

I have a table that has two columns:

user_id int(11) friend_id int(11)

In order to get the number of friends a user has has, I'd do something like:

SELECT COUNT(user_id) AS num_friends FROM friends WHERE friend_id = some_id

What I want to do is get the number of friends every single user in the table has and cache the results to another table so I don't have to run an expensive query like the one above. Basically a 'rollup'.

What's the best way to query for this data to get the number of friends that every friend_id has? I was thinking something like this, but it seems very wrong, especially if you have a large amount of distinct friend_id instances in the table:

SELECT COUNT(*) num_friends, friend_id FROM friends WHERE friend_id IN ( SELECT DISTINCT(friend_id) FROM FRIENDS) GROUP BY friend_id

Answer 1

SELECT friend_id, COUNT(*)
    FROM num_friends
    GROUP BY friend_id;

This will assist in making it fast:

INDEX(friend_id)

With that index, you will find out that the original query is so fast that you don't need to cache the results.