mysql performance tip Follow @yiiframework

When we are doing an insert of huge no.of rows into an SQL table ,Normally we will try to write separate insert quries this may lead us to a long duration of execution time and we can increase the speed of executing SQL quries by adding all the rows into a single insert query.

NORMAL CASE:

eg:INSERT INTO tbl_name(field1,field2,…) values(value1,value2,..);
    INSERT INTO tbl_name(field1,field2,…) values(value1,value2,..);
    INSERT INTO tbl_name(field1,field2,…) values(value1,value2,..);

We can decrease the execution time by adding all these rows into a single INSERT query like below,

INSERT INTO tbl_name(field1,field2,…) values(value1,value2,..),values(value1,value2,..),values(value1,value2,..),….;

This is useful when we are trying to insert about 10k of rows into a table at a time. For me normally it takes 1min 15 sec to insert 10k of rows but by using this tip it takes only 10 sec to insert 10k of rows.

i have tried this when i did excel and csv import feature. For excel it took around 20 sec and for csv it took 10 sec of execution time for inseting 10k of rows.

Reagards,

sirin k

Total 6 comments

#9748

sirin k at 2012/09/08 01:12am

Can you tell me

Can you tell me how to set autocommit=0 ?

#9747

jpablo at 2012/09/08 01:07am

Transactions will really speed up a group insert

You should really pay attention to set autocommit=0 The dafault autocommit creates a transaction for each insert, with a huge overhead. Just disable it and the group insert will run more than 10 times faster.

#9716

Mariusz W. at 2012/09/05 11:43am

better way to speed up

If you want to insert many rows and you use innodb engine you should turn off checking foreign keys before start inserting and at the end turn on checking foreign keys.

#9675

MAHESH.E.U at 2012/09/03 04:10am

Thanks

This would be very useful. Thanks Sirin

#9661

sirin k at 2012/09/01 10:01pm

hi François Gannaz

Thank you very much for your useful review.Anyway im changing its title to mysql tip as you have mentioned some issues on SQL standards.also its works fine in mysql.

i have also written an article about how you can use "LOAD DATA".check here http://www.yiiframework.com/wiki/336/importing-csv-file-to-mysql-table-using-load-data-command/

Thanks,

-Sirin

#9654

François Gannaz at 2012/09/01 03:31pm

A bit useful, but shallow

The tip is partly right, but it lacks important information. I believe it should be part of a larger page.

A few remarks:

This tip is wrong in standard SQL. The syntax proposed is right for MySQL, but it won't work with some other DB (IIRC, SQL-Server doesn't accept this syntax).
For DB engines that allow this syntax, it may not provide a huge performance gain. For instance, with SQLite, better performance can be reached with PRAGMA synchronous = OFF (beware of the side-effects).
With MySQL, there is a max_allowed_packet variable that limits the size of the query one can send to the server. So grouping too many rows can lead the query to a complete failure.
When not using a multi-rows insert, the recommended way is to use prepared statements: it is safer and faster than independent queries.
Yii plays no role in this problem, so the best solution is to follow the official procedure for the DB. For MySQL, the documentation Speed of INSERT Statements is very useful. I especially recommend disabling the indexes before inserting. LOAD DATA can also be an excellent alternative to INSERT.

Please login to leave your comment.

mysql performance tip Follow @yiiframework

Total 6 comments

Leave a comment

Yii Supporters