Yii 1.1: mysql performance tip


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.


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.


sirin k

Hire Yii developer

Total 2 comments

#9661 report it
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/



#9654 report it
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.

Leave a comment

Please to leave your comment.

Write new article
  • Written by: sirin k
  • Category: Tips
  • Yii Version: 1.1
  • Votes: +2 / -2
  • Viewed: 10,123 times
  • Created on: Sep 1, 2012
  • Last updated: Apr 25, 2017
  • Tags: mysql