How to optimize a nested query?

Question

Can I somehow join tables and avoid the usage of distinct in the following MySQL query. invited_by_id shows the user id of who invited this user.

SELECT
    user1.id, count(distinct user2.id) AS theCount, count(distinct user3.id) AS theCount2
FROM
    users AS user1
LEFT OUTER JOIN
    users AS user2 ON user2.invited_by_id=user1.id
LEFT OUTER JOIN (
    SELECT id, invited_by_id FROM users WHERE signup_date >= NOW() - INTERVAL 30 DAY
) AS user3 ON user3.invited_by_id=user1.id
GROUP BY user1.id;

Answer 1

If you're running a version of MySQL greater than 5.0.37 you have a Profiler available to you that could give you a pretty good idea of where the bottlenecks are on any query. That might be a good starting point- you could maybe edit the output into the original question if you're not sure about how best to interpret it.

Answer 2

I am assuming here that you are trying to get a count of how many times a user has been invited and a count of how many times that user has been invited in the past 30 days.

In this case you could do the query with a simple conditional sum as :

select user1.id, count(user2.id) as tehCount, sum(user2.signup_date >= NOW() - INTERVAL 30 DAY) as theCount2
from users as user1
left outer join users as user2 on user2.invited_by_id = user1.id
group by user1.id

If the nulls in theCount2 will be a problem, use a coalesce as :

coalesce(sum(user2.signup_date >= NOW() - INTERVAL 30 DAY), 0)

Answer 3

Try something like this, I changed the sub-query table names to make it a bit clearer:

Select
    user.id,
    all_time.total AS theCount, 
    last_month.total AS theCount2
From users AS user
Left Outer Join 
    (Select Count(id) as total, invited_by_id
     From users
     Group By invited_by_id) as all_time
       On all_time.invited_by_id = user.id
Left Outer Join
    (Select Count(id) as total, invited_by_id
     From users 
     Where signup_date >= NOW() - INTERVAL 30 DAY
     Group By invited_by_id) AS last_month 
       On last_month.invited_by_id = user.id

If this is something you run often, make sure that user.invited_by_id is indexed!