Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 39.4Mb
PDF (A4) - 39.4Mb
PDF (RPM) - 38.5Mb
HTML Download (TGZ) - 10.9Mb
HTML Download (Zip) - 11.0Mb
HTML Download (RPM) - 9.6Mb
Man Pages (TGZ) - 217.4Kb
Man Pages (Zip) - 327.1Kb
Info (Gzip) - 3.6Mb
Info (Zip) - 3.6Mb
Excerpts from this Manual Optimizing INSERT Statements

To optimize insert speed, combine many small operations into a single large operation. Ideally, you make a single connection, send the data for many new rows at once, and delay all index updates and consistency checking until the very end.

The time required for inserting a row is determined by the following factors, where the numbers indicate approximate proportions:

  • Connecting: (3)

  • Sending query to server: (2)

  • Parsing query: (2)

  • Inserting row: (1 × size of row)

  • Inserting indexes: (1 × number of indexes)

  • Closing: (1)

This does not take into consideration the initial overhead to open tables, which is done once for each concurrently running query.

The size of the table slows down the insertion of indexes by log N, assuming B-tree indexes.

You can use the following methods to speed up inserts:

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Rick James on April 22, 2017
Some Rules of Thumb:

* A million rows will have a BTree depth of 3; a trillion rows, 6. That is, the "fan out" is 100, and the 'log' is base 100. (Of course there are extremes of perhaps 2 to 1000 instead of 100.)

* A batched, multi-row INSERT with 100 rows will run 10 times as fast as 100 single-row INSERTs. (The 10x is from real experiences, and it somewhat approximates the 'proportions' given on this page.)

* Don't go past 1000 rows in a batched insert -- especially if there is contention for the table and/or replication is involved.

* Another optimization is to pre-sort the rows to insert (via LOAD DATA or batched INSERT) according to some index, preferably the PRIMARY KEY or some UNIQUE key. This helps the flow of blocks through cache and helps avoid deadlocks.