imran dodhiya imran dodhiya - 2 months ago 7
MySQL Question

Decrease find duplicate record query execution time

I have 3 lac of records. I need to count duplicate records and return all duplicate records (Ex. if example@example.com are 10 times then returns all 10 records with duplicate of 10)

I have created query for that but it takes 15 seconds of time. Any suggestion to decrease time?

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

Need to decrease the data for join condition by adding having condition like below query.

Also make sure that there should be an index on email column to optimize it

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
Comments