An example of deduplication Optimization for a XXX mobile phone company

This is an SQL statement they developed to delete duplicate data and the row whose id is the minimum value is not deleted: deletefromjd_chapterawherea. 'id' in (select 'id' fromjd _ chaptergroup & nbs ..

This is an SQL statement they developed to delete duplicate data and the row whose id is the minimum value is not deleted: deletefromjd_chapterawherea. 'id' in (select 'id' fromjd _ chaptergroup & nbs ..

This is an SQL statement they developed to delete duplicate data and the row whose id is the minimum value is not deleted:

Because the table is large (tens of millions) and two subqueries are used, the execution has not been completed for a long time.

-------------------------- Train of Thought ----------------------------

To associate a temporary table, follow these steps:

1. Export duplicate data to the Slave database first to ensure the website space is sufficient.

Into outfile '/tmp/jd_chapter. SQL' FIELDS TERMINATED ',';

2. Copy the exported SQL to the/tmp/directory of the Master database.

3. Create a temporary table on the Master database and create a primary key:

Query OK, 0 rows affected (0.07 sec)

4. import data to the temporary table in the LOAD mode on the Master database of the Master database

Load data infile '/tmp/jd_chapter. SQL' into table tmp FIELDS TERMINATED ',';

5. Delete the smallest id of the temporary table on the Master database of the Master database.

Delete from tmp limit 1;

6. Use a temporary table for association and delete duplicate data in the jd_chapter table

This article is from the "hechun's technical column" blog, website space, please be sure to keep this source

, Virtual host