title: PHP mySQL performance issues on large series of insert statements, should I use something else?
body: <p>I have a script that compiles a list of items that can be very long - just compiling this list can take 10-15 minutes, but that is acceptable.  When I incorporate a function that iterates through the list and inserts them all into a mySQL table, that time is increased about about 50%.  I was wondering if there was a faster way of serializing this data?  Should i explore CSV or something else?  Or can I optimize my code to do this faster:</p>
  
  <pre><code>    private function toDB(){
      $sql[] = "DROP TABLE IF EXISTS checklisttest";$sql[] = "CREATE TABLE checklisttest (
    Incident varchar(12) NOT NULL,
    TestID mediumint(9) NOT NULL AUTO_INCREMENT,
    Element varchar(12) NOT NULL,
    Name varchar(128) NOT NULL,
    Code varchar(512) NOT NULL,
    Expected varchar(512) NOT NULL,
    Actual varchar(512) NOT NULL,
    AutoVerifyResult varchar(32) NOT NULL,
    QAResult varchar(32) DEFAULT NULL,
    Comments text,
    PRIMARY KEY (TestID)
  )";
  
      //iterate through the records $this-&gt;records[10001] -- There can be anywhere from 100 - 300 records
      foreach($this-&gt;records as $inc =&gt; $record){
          //iterate through the element ids $this-&gt;records[10001][E02_04]
          foreach($this-&gt;records[$inc]["Elements"] as $elementID =&gt; $element){
              //iterate through the element ids $this-&gt;records[10001][E02_04][1] --There can be anywhere from 150 - 350 elements per record.
              foreach($element as $key =&gt; $val){
                  $sql[] = "
  INSERT INTO `checklistTest` VALUES (\"$inc\",NULL,\"$elementID\",\"$val[name]\",\"$val[code]\",\"$val[expected]\",\"$val[actual]\",\"$val[match]\",\"$val[QAResult]\",NULL)";
              }
          }
      }
      foreach($sql as $key =&gt; $val){
          mysql_select_db("new",$GLOBALS['local']);
          mysql_query($val,$GLOBALS['local']) or die(mysql_error());
      }
      //echo "&lt;textarea style='width:100%;height:400px'&gt;$sql&lt;/textarea&gt;";
      //mysql_select_db("new",$GLOBALS['local']);
      //mysql_query($sql,$GLOBALS['local']) or die(mysql_error());
  }
  </code></pre>
  
  <p>There must be a better way to go about doing this, I just don't have much experience performing a lot of queries like this - usually they are just one-and-done for me.  Thanks for the help.</p>
  
  <p>thanks for the answers, I posted my solution in a comment to the accepted answer.</p>