I have 3 lac of records. I need to count duplicate records and return all duplicate records (Ex. if email@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
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