Efficient query of repeated fields in a MySQL large table _ MySQL

How to query duplicate fields in a large MySQL table using bitsCN. comMySQL statements? This is a problem that many people have encountered. The following describes how to query duplicate fields in a MySQL large table for your reference.

There is a large table in the database. you need to find a duplicate record id in the name for comparison.
It is easy to find fields with unique names in the database.

SELECT min ('id'), 'name'
FROM 'table'
Group by 'name ';

However, the id value of the repeated field cannot be obtained. (Only the smallest id value is obtained)
It is easy to query which fields are repeated.

SELECT 'name', count ('name') as count
FROM 'table'
Group by 'name' HAVING count ('name')> 1
Order by count DESC;

However, to query the id of a repeated field at a time, you must use the subquery. Therefore, use the following statement to query duplicate fields in a MySQL large table.

SELECT 'id', 'name'
FROM 'table'
WHERE 'name' in (
SELECT 'name'
FROM 'table'
Group by 'name' HAVING count ('name')> 1
);

However, this statement is too inefficient in mysql and mysql does not generate a temporary table for the subquery.
Create a temporary table first

Create table 'tmptable' (
SELECT 'name'
FROM 'table'
Group by 'name' HAVING count ('name')> 1
);

Then use multi-table connection to query

SELECT a. 'id', a. 'name'
FROM 'table'a, 'tmptable' t
WHERE a. 'name' = t. 'name ';

The result is coming soon.

Duplicate with distinct

SELECT distinct a. 'id', a. 'name'
FROM 'table'a, 'tmptable' t
WHERE a. 'name' = t. 'name ';

BitsCN.com