Delete duplicate data from a MySQL big data table

If you use the Select name from table where name in (Select name from Table group by name having count (name)> 1) query for fields that have no index, the efficiency is very low and it is not desirable. The following describes the alternative steps:

1. Create a temporary table based on Repeated Records

Create Table temptable (

Select title from video

Group by title having count (title)> 1

);

2. query duplicate data

Select a. * From temptable T, video a where a. Title = T. title;

 

The script used to delete duplicate data is provided below for your reference:

 Create   Table Temptable As  (  Select   Max (ID)As ID From  Video  Group   By Title Having   Count (Title) >  1  );  Delete    From Video Where ID In ( Select IDFrom Temptable) Order   By  ID;  Drop   Table Temptable;