Why Using INSERT INTO SELECT Cost My Company $10k – A MySQL Post‑mortem

Background

The company processes millions of rows daily in a single MySQL table without sharding, and needed to migrate old data to a history table to keep performance acceptable.

Proposed Solutions

Programmatically query the data, insert into the history table, then delete the original rows.

Use INSERT INTO SELECT so the database handles the entire operation.

First Solution Failure

The pseudocode for the first approach was:

// 1. Query data to migrate
List<Object> list = selectData();
// 2. Insert into history table
insertData(list);
// 3. Delete original rows
deleteByIds(ids);

Loading all rows into memory caused an out‑of‑memory (OOM) crash; batch processing reduced I/O but was still too slow.

Second Solution Deployment

The team chose the INSERT INTO SELECT method, adding a date filter to keep only the last ten days (about 10 k rows). They scheduled it as a nightly job at 8 pm, and tests in a staging environment passed without issue.

Production Incident

After the nightly run, the finance team discovered mismatched payment records: many transactions failed to insert, resulting in a loss of nearly ¥100 k. Disabling the migration job stopped the failures.

Root‑Cause Analysis

The EXPLAIN output (shown in the image) revealed a full‑table scan on the source table. Under MySQL’s default isolation level, INSERT INTO a SELECT b locks table a entirely while locking rows of b one by one, leading to lock contention and intermittent insert failures.

Because the migration took about an hour, the lock held during the night prevented other transactions from acquiring the necessary row locks, causing time‑outs and lost inserts.

Why Tests Missed the Issue

The test environment used realistic data volumes but did not replicate the high‑concurrency, high‑throughput load of production, especially the massive batch insert that occurs at night.

Solution

Prevent the full‑table scan by adding an index on the WHERE clause used in the INSERT INTO SELECT. With the index, the SELECT uses index lookups, the operation finishes quickly, and the lock on the target table is held for a short period.

Conclusion

While INSERT INTO SELECT can simplify data migration, it must be used with proper indexing and an understanding of its locking behavior; otherwise, it can cause severe data loss and financial impact.