Search code examples
mysqlsqlsocial-networking

Count 2 columns on different conditions

I have this dataset:

id  uid    follows_uid  status      
1   1       2           ACTIVE
2   1       3           ACTIVE
3   3       1           ACTIVE
4   4       1           ACTIVE
5   2       1           ACTIVE

on giving uid I want to calculate how many users are following, and how many are followed by (the given user).

Result set will be:

following     followers
2             3

and here is the query which does the work:

SELECT COUNT(*) as following, 
    (SELECT COUNT(*) FROM user_followers where follows_uid = 1  ) as followers
FROM user_followers
WHERE uid = 1 and `status` = 'ACTIVE'

Now the question is, In't there any other way to get this done? Or is it the best way to achieve this?

Solution

  • If you have separate indexes on uid and follows_uid, then I believe using subqueries as you did is the fastest way to retrieve the separate counts because each query will take advantage of an index to retrieve the count.