MySQL batch insert speed

About MySQL insert speed: http://dev.mysql.com/doc/refman/5.0/en/insert-speed.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)

In addition, rewriteBatchedStatements=true is the important parameter. rewriteBatchedStatements=true improves the performance so dramatically by rewriting of prepared statements for INSERT into multi-value inserts when executeBatch() (Source). That means that instead of sending the following nINSERT statements to the mysql server each time executeBatch() is called :

INSERT INTO X VALUES (A1,B1,C1)
INSERT INTO X VALUES (A2,B2,C2)
...
INSERT INTO X VALUES (An,Bn,Cn)
It would send a single INSERT statement :
INSERT INTO X VALUES (A1,B1,C1),(A2,B2,C2),...,(An,Bn,Cn)
Labels: , , ,