How to Make SQL Insert Statement Simply Faster?

If you have lots of rows to insert into a MySQL table, you might experience it taking ages to complete. For example, I have around 100K rows to insert into a table, and it takes nearly half day to complete. The SQL statements look like this:

insert into Data set ColA=A1, ColB=B1, ColC=C1;
insert into Data set ColA=A2, ColB=B2, ColC=C2;
insert into Data set ColA=A3, ColB=B3, ColC=C3;
insert into Data set ColA=A4, ColB=B4, ColC=C4;
...
...
insert into Data set ColA=An, ColB=Bn, ColC=Cn;

This is extremely inefficient as the MySQL will process each insert statement separately. In MySQL console, it will still print out the lots of messages like (each insert prints 1 message to the console):

Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.02 sec)
Query OK, 1 row affected (0.01 sec)

Printing out to console takes time and slow the process down especially for 100K rows of data. So the better syntax to insert multiple rows to a MySQL table should be:

insert into Data values 
  (A1, B1, C1),
  (A2, B2, C2),
  (A3, B3, C3),
 ...
 ...
  (An, Bn, Cn);

Now, the MySQL will treat all these into 1 single, big insert statement. MySQL will only print 1 message after all data is inserted. And it is simply a lot faster to process, e.g.:

Query OK, 373930 rows affected (1 min 18.54 sec)
Records: 373930  Duplicates: 0  Warnings: 0

MySQL

–EOF (The Ultimate Computing & Technology Blog) —