title: Why does TRANSACTION / COMMIT improve performance so much with PHP/MySQL (InnoDB)?
body: <p>I've been working with importing large CSV files of data; usually less than 100,000 records. I'm working with PHP and MySQL (InnoDB tables). I needed to use PHP to transform some fields and do some text processing prior to the MySQL <code>INSERT</code>s (part of <code>process_note_data()</code> in code below). MySQL's <code>LOAD DATA</code> was not feasible, so please do not suggest it.</p>
  
  <p>I recently tried to improve the speed of this process by using MySQL transactions using <code>START TRANSACTION</code> and <code>COMMIT</code>. The performance increase was surprising. Processing time(s) dropped by a factor of 20. So, a 20 minute process only took about 1 minute.</p>
  
  <p>QUESTIONS.</p>
  
  <p>1.) Does anyone understand why there was such performance increase (20 mins to 1 min)?</p>
  
  <p>2.) Should I be concerned about how big the transaction may get with 100,000 records?</p>
  
  <p>3.) Should I be concerned with a large number of inserts and/or updates in the transaction?</p>
  
  <pre><code>/*
   * Customer Notes Data:
   * Rows are either a meeting, call or note!
   */
  $row = 1;
  $data = array();
  $fields = array();
  $line = '';
  
  $db-&gt;query('SET autocommit=0;');
  $db-&gt;query('START TRANSACTION;');
  
  if (($handle = fopen("modules/".$currentModule."/Data/customernote.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 4096, ',', '"')) !== FALSE &amp;&amp; $row &lt; 999000) {
      //Row 1 - CSV header row with field names
      if ($row == 1) {
        $csv_fields = $data;
      } elseif ($row &gt; 1) {
        $fields = $this-&gt;process_note_data($data, $csv_fields, $row);
      }
      $row++;
    } // end while
    fclose($handle);
  }
  
  $db-&gt;query('COMMIT;');
  $db-&gt;query('SET autocommit=1;');
  </code></pre>
  
  <p>Note: The text/field processing is done in the call to <code>$this-&gt;process_note_data()</code> which then calls another helper class that has the <code>INSERT</code> statement code. I didn't have enough room to include all of the code. <code>$db-&gt;query()</code> is a typical database object for MySQL queries.</p>