Why does TRANSACTION / COMMIT improve performance so much with PHP/MySQL (InnoDB)? -

i've been working importing large csv files of data; less 100,000 records. i'm working php , mysql (innodb tables). needed use php transform fields , text processing prior mysql inserts (part of process_note_data() in code below). mysql's load data not feasible, please not suggest it.

i tried improve speed of process using mysql transactions using start transaction , commit. performance increase surprising. processing time(s) dropped factor of 20. so, 20 minute process took 1 minute.

questions.

1.) understand why there such performance increase (20 mins 1 min)?

2.) should concerned how big transaction may 100,000 records?

3.) should concerned large number of inserts and/or updates in transaction?

/*  * customer notes data:  * rows either meeting, call or note!  */ $row = 1; $data = array(); $fields = array(); $line = '';  $db->query('set autocommit=0;'); $db->query('start transaction;');  if (($handle = fopen("modules/".$currentmodule."/data/customernote.csv", "r")) !== false) {   while (($data = fgetcsv($handle, 4096, ',', '"')) !== false && $row < 999000) {     //row 1 - csv header row field names     if ($row == 1) {       $csv_fields = $data;     } elseif ($row > 1) {       $fields = $this->process_note_data($data, $csv_fields, $row);     }     $row++;   } // end while   fclose($handle); }  $db->query('commit;'); $db->query('set autocommit=1;'); 

note: text/field processing done in call $this->process_note_data() calls helper class has insert statement code. didn't have enough room include of code. $db->query() typical database object mysql queries.

1. please check link:

   https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-transaction-management.html

   innodb must flush log disk @ each transaction commit if transaction made modifications database. when each change followed commit (as default autocommit setting), i/o throughput of storage device puts cap on number of potential operations per second.

2. big transactions may affect performance during commit (check above)

3. only in case of rollback, may optimized using settings (check link)