Lessons Learned from Using INSERT INTO SELECT for MySQL Data Migration

In a high‑traffic MySQL environment, a colleague attempted to migrate millions of rows by either loading data into memory and inserting into a history table or by using INSERT INTO SELECT to let the database handle the operation. The first approach caused an OOM crash, while the second appeared to work in testing but later caused data loss in production.

The root cause was a full table scan triggered by the INSERT INTO SELECT statement, which, under the default transaction isolation level, locked rows one by one. This led to intermittent insert failures and eventually missing payment records.

// 1. Query data to migrate
List<Object> list = selectData();

// 2. Insert data into history table
insertData(list);

// 3. Delete original rows
deleteByIds(ids);

Testing had used realistic data volumes but did not simulate the concurrent load and timing of the nightly migration task, so the issue was missed until it occurred in production.

To prevent similar incidents, the article recommends adding appropriate indexes to the WHERE clause of the SELECT, avoiding full table scans, and being cautious with bulk INSERT INTO SELECT operations on large tables. Proper indexing ensures the query uses an index scan rather than a full table scan, reducing lock contention and execution time.

In summary, while INSERT INTO SELECT can simplify data migration, it must be used with careful consideration of transaction isolation, indexing, and the size of the data set to avoid performance problems and data loss.