Speedup MySQL inserts

Speedup MySQL inserts

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.

About the author

Yuli Stremovsky
Yuli StremovskyParanoid Security Guy
In my daily life, I am the founder of a GDPR privacy automation service available at https://privacybunker.io/. I maintain the following open-source privacy project https://databunker.org/.

Among my credits, I was a founder of a database security company GreenSQL/Hexatier which was acquired by Huawei.

Specialties: Software and cloud architecture, Compliance (GDPR, HIPAA, PCI, SOX), blockchain technologies, software development, secure architectures, project management, and low-level research.