How to speed up multiple INSERTs in Mysql?

Question

MySQL

I have a php script that executes a while loop that repeats 10,000 times and does 1 INSERT into the database each time.
As a result, the script execution time reaches almost a minute .. can I speed it up somehow?

Answer 1

You can shove the whole thing into a transaction, so the indexes are recalculated 1 time.
True, I would recommend a batch insert in a cycle. In the sense to fill a request for N inserts with one insert, execute, then fill it again and execute, etc.

Answer 2

Put the data into CSV (with fopen(), fputcsv()) and then use LOAD DATA INFILE (for MySQL) or BULK INSERT (for SQL Server). It will be ultra fast, since the instructions above were created specifically for bulk uploads.

Answer 3

Certainly. Don't do it in a loop, but do it in batches. By the way, it would be nice to show the code and explain why such a cycle is necessary.

Answer 4

What prevents to collect one big request and make one request:

INSERT INTO MyTable ( Column1, Column2 ) VALUES
( Value1, Value2 ), ( Value1, Value2 ), ( ValueN, ValueN )

Answer 5

LOAD DATA INFILE