title: php pdo efficient way to insert too many rows at a time
body: <p>I'm programmatically importing rows from an excel file to database. The excel file has 10 columns * 30000 rows. I've imported those data in php array and then it is inserted in database.</p>
  
  <p>After uploading file it takes 7-8 minutes to insert all rows in database. I know two ways of inserting the rows</p>
  
  <p><strong>1st method:</strong></p>
  
  <p>Generate dynamic query something like,</p>
  
  <pre><code>INSERT INTO table_name (col1, col2,..) VALUES (row1, row1, ....), (row2, row2, ....), (row3, row3, ....), (row4, row4, ....), ...
  </code></pre>
  
  <p>and run the query to insert all rows.</p>
  
  <p><strong>2nd method:</strong></p>
  
  <pre><code>$con-&gt;prepare("INSERT INTO table_name (col1, col2, ...) VALUES (:col1, :col2, ...)");
  foreach ($rows as $row) { // Loop through all rows and insert them
      $result-&gt;bindParam(':col1', $val1);
      $result-&gt;bindParam(':col2', $val2);
      $result-&gt;bindParam(':col3', $val3);
      ...
      ...
      $result-&gt;execute();
  }
  </code></pre>
  
  <p>1st seems messy and inefficient and I'm using 2nd method but it insert only 500-700 rows per second and takes 7-8 minutes overall time to insert all rows.</p>
  
  <p>What other methods which are efficient and faster than these ? </p>
  
  <p>EDIT : Do not suggest to import excel file directly to mysql. The data need to be processed before inserting into database.</p>