MySQL large table repeated field query efficiency

MySQL large table repeated fields should be how to find it? This is a lot of people have encountered the problem, here is to teach you a MySQL table repeated fields of inquiry, for your reference.

The database has a large table, you need to find the name of the duplicate record id, in order to compare.

If you just find the name of the database does not repeat the field, it is easy

SELECT min (`id`),` name` FROM `table` GROUP BY` name`;

But this does not get the id value that says there are duplicate fields. (Only got the smallest id value)

It is also easy to find out which fields are duplicates

SELECT `name`, count (` name`) as count FROM `table` GROUP BY` name` HAVING count (`name`)> 1 ORDER BY count DESC;

However, to check the repeated field id value, you must use the subquery, so use the following statement to MySQL large table repeated field queries.

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, feeling mysql did not generate a temporary table for the subquery.

So use the first to create a temporary table

create table `tmptable` as (SELECT` name` FROM `table` GROUP BY` name` HAVING count (`name`)> 1);

Then use multi-table join query

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

The result came quickly.

Repeat with distinct

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