Sunday, September 30, 2012

Speedup mysql inserts

By default, when connected to 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 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.

The solution for this problem is very easy. You need to prevent MySQL from committing transaction after every INSERT and commit 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 scripts 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.


No comments:

Post a Comment