How to increase insert speed in InnoDB table?

Question

MySQL

Is it possible to increase insert speed in innodb? Myisam table fills up almost instantly with 2 million records, but innodb takes a terribly long time. In contrast, update in myisam is very slow, and in innodb (according to information) much faster. I need to choose one type and set up the database so that both the insertion and the update happen quickly. Can you somehow tweak the settings? Thank you!

Answer 1

BEGIN TRANSACTION
-- Insert rows in batches of 10-100K
COMMIT

Answer 2

Yes, it seems that the updates were faster in Maisame.

Answer 3

Use INSERT with multiple VALUES:

INSERT INTO table (col1, col2)
VALUES
(1,2),
(3,4),
....

This is much faster than inserting one row at a time - the indexes will be rebuilt once after the transaction completes. Or a faster way - LOAD DATA INFILE, bulk loading of data from an external (local relative to mysqld) file.

Answer 4

disable indexes, disable transactional nafik, upload data via LOAD DATA INFILE, enable.