0

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
imaginative
  • 127
  • 3
  • What you are looking for might be window function, which I believe still doesn't exist for MySQL. See this similar question for lead....https://dba.stackexchange.com/questions/40130/mysql-and-window-functions – Kentaro Aug 04 '16 at 02:30
  • Heh, I was writing an answer to that INT-LIKE question when you deleted it ;) – ypercubeᵀᴹ Dec 20 '17 at 19:28

1 Answers1

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.

Rick James
  • 78,038
  • 5
  • 47
  • 113