Posted on May 20, 2017 at 10:23 am

Fastest ways to find duplicate records in MySQL.

If name is the column you want to find duplicates in:

SELECT col1 FROM table GROUP BY col1 HAVING count(*) > 1;

Is also possible to support multiple columns, like this:

SELECT col1, col2, COUNT(*) c FROM table GROUP BY col1, col2 HAVING c > 1;

Get the IDs of the duplicate rows with GROUP_CONCAT:

SELECT GROUP_CONCAT(id), name, COUNT(*) c FROM table GROUP BY name HAVING c > 1;

Other Posts

• Quota 'IN_USE_ADDRESSES' exceeded. Limit: 8.0 in region
• Only allow Google Load Balancer traffic (Firewall Rules)
• Lost connection during Debian apt-get upgrade
• Bootstrap 3 Navbar with Logo Image
• Redis HMGET, HGETALL, HSET, HGET Benchmark
• PHP GetHostByName() Slow? Make it Faster
• Use Timestamp with new DateTime() in PHP
• Calculate Elapsed Time Between Two Dates in PHP

Updated Posts

• Let's Encrypt on Nginx and SSL Grade A+
• Vertically align Facebook and Twitter share buttons
• Download Windows 10 ISO without Media Creation Tool
• DEB.SURY.ORG Updated the Signing Key (March 2019)
• Firefox add-ons cannot be verified and have been disabled
• Server Certificate Verification failed CAfile (apt-get update)
• Python Socket IPv6 (AF_INET6) Listen
• Allow User to Run Sudo Command without Password