Geek Answers Handbook

How to optimize a subquery?

Can I somehow join the tables and avoid using the individual in the next MySQL query. invite_by_id shows the identifier of the user 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;
+5
source share
3 answers

Try something like this, I changed the subquery table names to make it more clear:

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 you work often, be sure to get user.invited_by_idindexed!

+1
source

I assume that you are trying to count how many times a user has been invited, and counting the number of times a user has been invited in the last 30 days.

:

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

Count2 , coalesce :

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

If you are using a version of MySQL greater than 5.0.37, you have a Profiler that can give you a pretty good idea of ​​where the bottlenecks are on any query. This might be a good starting point - you could edit the output in the original question if you don't know how to interpret it better.

+1
source

More articles:


All Articles