如何优化嵌套查询？

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.

我可以以某种方式连接表并避免在以下MySQL查询中使用distinct。 invite_by_id显示邀请此用户的用户ID。

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;

3 个解决方案

#1

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!

如果这是您经常运行的内容，请确保将user.invited_by_id编入索引！

#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.

我在这里假设您正在尝试计算用户被邀请的次数以及过去30天内该用户被邀请的次数。

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 :

如果theCount2中的空值存在问题，请使用coalesce：

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

#3

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.

如果您运行的MySQL版本大于5.0.37，则可以使用Profiler，这可以让您非常了解任何查询的瓶颈所在。这可能是一个很好的起点 - 如果您不确定如何最好地解释它，您可以将输出编辑为原始问题。

#1