imran dodhiya imran dodhiya - 1 year ago 44
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 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,, b.totalCount AS duplicate_guest
FROM guest g
INNER JOIN (SELECT email, COUNT(Id) AS totalCount FROM guest GROUP BY email ) b ON =

Answer Source

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,, b.count as duplicate_guests
FROM guest g 
     SELECT email, COUNT(Id) AS count
     FROM guest  
     GROUP BY email 
     HAVING count(*) > 1
) b ON =