Tune INSERT SELECT requests ​

An INSERT ... SELECT reads rows, evaluates expressions and validates destination values in one statement. Diagnose these stages before raising a resource limit. A slow transfer can come from an expensive source query, conversion of wide values, or the amount of data being copied.

Start with the source ​

Run EXPLAIN QUERY PLAN on the SELECT and measure a representative range. Select the destination columns explicitly so an unrelated schema change cannot shift their positions.

INSERT INTO archived_orders(id, amount)
SELECT id, amount FROM orders WHERE id >= 1000 AND id < 2000;

Use nonoverlapping key ranges for a large backfill and record which ranges completed. An error or interrupted response needs reconciliation before retrying; repeating a successful range can duplicate rows unless the destination key or application design prevents it.

Compare measurements ​

Read terminal events in system.query_log for duration, read work, written work and peak memory. Compare the same data and settings before drawing conclusions. max_threads is a ceiling and does not make every INSERT SELECT parallel. Database SET changes apply to later requests and persist for other clients selecting that database.

See INSERT INTO, batch inserts and query history.