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

Question

我有3个lac的记录。我需要统计重复的记录并返回所有重复的记录（例如，如果exam​​[email protected]是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

需要通过添加有条件如下面的查询，以减少对加盟条件的数据。

还要确保应该对电子邮件列的索引来优化它

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 

Answer 2

只需添加HAVING count(*) > 1到内选择

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 
    HAVING count(*) > 1 
) b ON g.email = b.email