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
For the past 15 years I’ve been leading the evolution of startups and enterprises to achieve the highest level of security and compliance. Throughout my career I’ve been a Cyber Security expert and advanced solutions architect with many years of hands on experience both on offensive and defensive side. Knowledgeable at the highest level in application development, networking, data and databases, web applications, large scale Software as a Service solutions, cloud security and blockchain technologies.

I’ve been working with CISO’s of international enterprises, helping them set Information Security strategy, and overseeing the implementation of these recommendations. As part of these projects, I’ve been assisting companies to achieve compliance in GDPR, PCI, HIPAA and SOX.

Among my credits, I was a founder of a database security company GreenSQL/Hexatier which was acquired by Huawei and I’ve co-founded Kesem.io, Secure multi-signature Crypto wallet.

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