INSERT INTO SELECT Migration Disaster: Full Table Scan Locks Production

Background

The company handled high transaction volumes using MySQL without sharding, accumulating roughly one million new rows daily. To maintain performance, a data migration task was needed to move older data to a history table while retaining the most recent 10 days (about 10 million rows) in the main payment flow table.

Two Approaches Considered

Approach 1: Query data via application, insert into history table, then delete from source table.

Approach 2: Use INSERT INTO ... SELECT to let the database handle the entire operation internally.

First Approach: OOM Failure

The first approach loaded all matching rows into memory at once, causing an OutOfMemoryError. Batched processing was considered but rejected due to excessive I/O and long runtime.

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

Second Approach: Production Incident

The developer chose

INSERT INTO history_table SELECT * FROM payment_flow WHERE dateTime < '10 days ago'

. This avoided application‑side memory pressure and simplified code. A test environment with 10 million rows passed without issues. The job was scheduled as a nightly cron at 8 PM when traffic was low.

The next morning, financial reconciliation failed: many payment flows after 8 PM were missing. Investigation showed payment inserts started failing intermittently after the migration job began. Stopping the job eliminated the failures.

Root Cause Analysis

Full Table Scan

An EXPLAIN of the migration query revealed a full table scan on the 10‑million‑row payment flow table. The migration took roughly one hour, explaining why daytime operations (when the job wasn’t running) were unaffected.