Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.9Mb
PDF (A4) - 37.9Mb
PDF (RPM) - 37.2Mb
HTML Download (TGZ) - 10.2Mb
HTML Download (Zip) - 10.3Mb
HTML Download (RPM) - 8.9Mb
Man Pages (TGZ) - 214.6Kb
Man Pages (Zip) - 327.5Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

8.2.4.1 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
  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.
Sign Up Login You must be logged in to post a comment.