优化包含左联接的SQL查询

debugcn 发表于 Dev

穆巴拉克

我有1个称为错误的表，它具有以下结构：

失误

| id | UserID        | CrashDump   | ErrorCode| Timestamp
| 1  | user1         | Crash 1     | 100      | 2015-04-08 21:00:00 
| 2  | user2         | Crash 2     | 102      | 2015-04-10 22:00:00
| 3  | user3         | Crash 4     | 105      | 2015-05-08 12:00:00
| 4  | user4         | Crash 4     | 105      | 2015-06-02 21:22:00
| 5  | user4         | Crash 4     | 105      | 2015-06-03 04:16:00

我想获得包含以下数据的结果集：

所需的结果集

   CrashDump        | Error Count| Affected Users| 
    Crash 4         | 3          | 2             |  
    Crash 2         | 1          | 1             | 
    Crash 1         | 1          | 1             |

结果集将每个错误的计数作为错误计数和受影响的用户（收到此错误的不同用户）保存。

我已经可以使用以下查询获得所需的结果，但是事实证明，这是非常耗费资源的，并且在庞大的数据集上导致MySQL崩溃。您能指导我如何优化当前查询或指导我实现其逻辑的更好方法吗？任何帮助将不胜感激。

当前查询：

select B.CrashDump as CrashDump, B.B_UID as affected users, C.C_UID as ErrorCount  
from
(
    Select count(A.UserID) as B_UID, A.CrashDump, (A.timestamp) as timestmp, 
    (a.errorcode) as errorCde, (a.ID) as uniqueId
    from
    (   
        select UserID , CrashDump, timestamp,errorcode,id
        from errors 
        where Timestamp >='2015-04-08 21:00:00' and Timestamp <='2015-06-10 08:18:15'
        group by userID,CrashDump
    ) as A
    group by A.CrashDump
) as B

left outer join 
(
    select CrashDump , count(UserID) as C_UID
    from errors 
    where Timestamp >='2015-04-08 21:00:00' and Timestamp <='2015-06-10 08:18:15'
    group by CrashDump
) as C

On B.CrashDump = C.CrashDump

order by ErrorCount desc limit 0,10

穆巴拉克

这是有效的解决方案：

Select A.CrashDump, sum(A.ErrorCount) as ErrorC, count(A.AffectedUsers) 
From
(
SELECT
    CrashDump,
    COUNT(ErrorCode) AS ErrorCount,
    COUNT(DISTINCT UserID) AS AffectedUsers, UserID
FROM
    errors
WHERE 
    Timestamp >='2015-05-13 10:00:00' and Timestamp <='2015-05-14 03:07:00'

GROUP BY
    CrashDump, userID
) AS A
group by A.CrashDump

order by ErrorC desc limit 0,10

谢谢大家为达到预期效果所提供的帮助。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。