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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.