mysql - 减少查找重复记录查询的执行时间

Question

我有 3 条记录。我需要计算重复记录并返回所有重复记录(例如，如果 example@example.com 是 10 次，则返回所有 10 条记录，其中重复项为 10)

我已经为此创建了查询，但需要 15 秒的时间。有什么减少时间的建议吗？

SELECT g.guest_name, g.email, b.totalCount AS duplicate_guest
FROM guest g 
INNER JOIN (SELECT email, COUNT(Id) AS totalCount FROM guest  GROUP BY email ) b ON g.email = b.email

Answer 1

需要通过添加having条件来减少连接条件的数据，如下查询。

还要确保电子邮件列上应该有一个索引来优化它

SELECT g.guest_name, g.email, b.count as duplicate_guests
FROM guest g 
INNER JOIN 
(
     SELECT email, COUNT(Id) AS count
     FROM guest  
     GROUP BY email 
     HAVING count(*) > 1
) b ON g.email = b.email