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

by

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 should be obvious, that sending one large statement will save you an overhead of 7 per insert statement, which in further reading the text also says:

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.

More Related Contents:

  1. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '0
  2. “INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”
  3. How to set initial value and auto increment in MySQL?
  4. How to do a batch insert in MySQL
  5. LAST_INSERT_ID() MySQL
  6. Increment a database field by 1
  7. Prevent auto increment on MySQL duplicate insert
  8. Cast int to varchar
  9. MySQL trigger On Insert/Update events
  10. How to copy a row and insert in same table with a autoincrement field in MySQL?
  11. MySQL Error 1264: out of range value for column
  12. BULK INSERT in MYSQL
  13. Importing a CSV to MySQL with different date format
  14. How to insert values in table with foreign key using MySQL?
  15. Field ‘id’ doesn’t have a default value?
  16. MySQL INSERT INTO … VALUES and SELECT
  17. MySQL error 1241: Operand should contain 1 column(s)
  18. insert data from one table to another in mysql
  19. MySQL Insert & Joins
  20. Reserved word in column name – insert into MySQL [duplicate]
  21. What are practical differences between `REPLACE` and `INSERT … ON DUPLICATE KEY UPDATE` in MySQL?
  22. Mysql insert into 2 tables
  23. How to turn on/off MySQL strict mode in localhost (xampp)?
  24. Insert data into table with result from another select query
  25. MySQL optimizing INSERT speed being slowed down because of indices
  26. MySQL: Insert datetime into other datetime field
  27. MySQL – How to parse a string value to DATETIME format inside an INSERT statement?
  28. Incorrect integer value: ” for column ‘id’ at row 1
  29. django cannot connect mysql in docker-compose
  30. Is there a way to use ON DUPLICATE KEY to Update all that I wanted to insert?

Leave a Comment