Search code examples
mysqlinsertmariadbbenchmarking

Which is faster: multiple single INSERTs or one multiple-row INSERT?

I am trying to optimize one part of my code that inserts data into MySQL. Should I chain INSERTs to make one huge multiple-row INSERT or are multiple separate INSERTs faster?

Solution

  • https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html

    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)

    From this it is clear that sending one large statement will save you an overhead of 7 per insert statement. This brings us to the documentation that states:

    If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements.