How to make sql more efficient. Need just the count

I need to find number of users:

1. who have one or more fans
2. who have two fans
3. who have three fans

Here is the sql I developed开发者_如何转开发 to get answer to the #1

  SELECT users.id, count(fans.id)
    FROM users 
    JOIN fans on users.id = fans.user_id
GROUP BY users.id
  HAVING COUNT(fans.id) > 0

Above query works but I am getting all the user records and then I am calculating the size of the array. It has terrible performance when the number of users is in thousands. How do I refactor the query so that I could get only the count of users?

I am using mysql, but I might need for another project using postgresql.

A subquery will do nicely.

Select 
  Count(user.id) 
from 
  Users 
Where 
  (Select Count(fans.id) from Fans where user_id = users.id) > 0

Try this:

select usersWithOneOrMoreFans = sum( case when t.fan_count >= 1 then 1 else 0 end ) ,
       usersWithTwoFans       = sum( case when t.fan_count =  2 then 1 else 0 end ) ,
       usersWithThreeFans     = sum( case when t.fan_count =  3 then 1 else 0 end )
from ( select user_id as user_id   ,
              count(*) as fan_count ,
       from fans
       group by fans.user_id
     ) t

[edited to remove pointless table reference]

select num_users_with_fans, num_users_with_two_fans,num_users_with_three_fans
from(
    select count(1) as num_users_with_fans
    from(
        select users.id, count(fans.id)
         from users join fans on users.id = fans.user_id
         group by users.id
         having count(fans.id) > 0
    )a
cross join(
    select users.id, count(fans.id) as num_users_with_two_fans
     from users join fans on users.id = fans.user_id
     group by users.id
     having count(fans.id)=2
)b
cross join(
    select users.id, count(fans.id) as num_users_with_three_fans
     from users join fans on users.id = fans.user_id
     group by users.id
     having count(fans.id)=3
)c

Here's mine :

 SELECT nfans, count(*) AS nusers FROM
     (SELECT count(fans.id) AS nfans FROM fans GROUP BY user_id) foo
 GROUP BY nfans

This will give you the number of users "nusers" which have "nfans" fans.

If you want to clip the output to maximum 3 fans :

 SELECT nfans, count(*) AS nusers FROM
     (SELECT least(3,count(fans.id)) AS nfans FROM fans GROUP BY user_id) foo
 GROUP BY nfans

In this case, the bin "nfans=3" contains the number of users which have 3 or more fans.