Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
If you have a medium size table, say 100,000 rows, and you issue a single INSERT statement with a VALUES clause containing 5,000 or so short rows (3 columns of bigint) and the primary keys are in ascending order it might take X time to insert. If the keys are out of order, it might take 10x+ the time to insert.
However, if you insert out of order keys using INSERT INTO t1 (x,y,z) SELECT a,b,c FROM t2 you can process 100,000 rows with out of order keys in <x time (more than 10x as fast).
It seems that when inserting with INSERT INTO t1 (x,y,z) SELECT a,b,c FROM t2 all the unique checks and index building is done once where as if you insert multiple rows in a single INSERT INTO t1 (x,y,z) VALUES (1,2,3), (7,8,9), (4,5,6) the rows are processed MUCH more slowly.