By default, when connected to a MySQL server, every command is executed as a distinct transaction. As a result, after each INSERT SQL statement, MYSQL saves data to the database and commits the transaction. When your application inserts hundreds and thousands of records, your database is busy in saving new records all the time and you are loosing in speed.
Originally this article was published in 2012 on codeboost.com website.
The solution to this problem is very easy. You need to prevent MySQL from committing the transaction after every INSERT and commit the transaction, for example, every 50 records.
It can be easily done. You just need to issue “START TRANSACTION;” SQL statement, insert your records, and when the counter reaches 50 new records, do commit “COMMIT’;
You can easily do it in a web-based script and in stand-alone applications.
For example:
START TRANSACTION;
INSERT INTO TABLE t1 (f1,f2,f3) values ('v1','v2','v3');
INSERT INTO TABLE t1 (f1,f2,f3) values ('v1','v2','v3');
INSERT INTO TABLE t1 (f1,f2,f3) values ('v1','v2','v3');
INSERT INTO TABLE t1 (f1,f2,f3) values ('v1','v2','v3');
-- more inserts
COMMIT;
After the COMMIT query, all new records are flushed and written to disk.
This method gives in some cases up to 10x performance boost.
It can be applied to other types of databases too.