CODEWITHSUNDEEP
phpmysqlpdo
hungneox
hungneox

Reputation: 9829

Efficient way to insert 1000 rows to MySQL with PDO

I have to insert 1000 rows of data per time into MySQL. At the moment, I use PDO and for-loop to insert row by row to database. Is there any more efficient way to achieve better performance? Because I have to set max_execution_time to 5 minutes.

function save()
{
            return $query = $this->insert("
                INSERT INTO gadata (landing_page, page_title, page_views, visits, visitors, bounce_rate, pageviews_per_visit, time_on_page, avg_time_on_page, day, month, year, hour)
                VALUES (:landing_page, :page_title, :page_views, :visits, :visitors, :bounce_rate, :pageviews_per_visit, :time_on_page, :avg_time_on_page, :day, :month, :year, :hour)", $this->data);
}

And

protected function insert($sql, array $data) {
    $q = $this->_db_handler->prepare($sql);

    foreach ($data as $k => $v)
    {
       $q->bindValue(':' . $k, $v);
    }

    $q->execute();
}

Upvotes: 0

Views: 1485

Answers (3)

Your Common Sense
Your Common Sense

Reputation: 157870

It is not PDO nor the way you are inserting makes insert so delayed, but innodb engine. So you have 3 choices:

  1. Wrap all inserts into transaction.
  2. using root privileges, set innodb_flush_log_at_trx_commit variable to 2, to make innodb use a filecache for writes - it will make your inserts blazingly fast.
  3. Run all the inserts in one query as suggested by Manu

Upvotes: 2

Eugen Rieck
Eugen Rieck

Reputation: 65274

Parameterized queries by definition trade execution safety against reduced flexibility on the count of data items.

You have at least 2 possibilities to mitigate:

  • Build up the SQL, then execute it at once:

Something like:

$sql="INSERT INTO gadata (landing_page, page_title, page_views, visits, visitors, bounce_rate, pageviews_per_visit, time_on_page, avg_time_on_page, day, month, year, hour) VALUES ";
foreach ($all_data_rows as $data) {
  if ($i==0) $value=""; else $value=",";
  $sql.=$value."(:landing_page$i, :page_title$i, :page_views$i, :visits$i, :visitors$i, :bounce_rate$i, :pageviews_per_visit$i, :time_on_page$i, :avg_time_on_page$i, :day$i, :month$i, :year$i, :hour$i)";
  $i++;
}
$i=0;
$q=$db_handler->prepare($sql);
foreach ($all_data_rows as $data) {
  foreach ($data as $k => $v) {
    $q->bindValue(":$k$i", $v);
  }
  $i++;
}
$q->execute();
  • Use a temporary table to avoid locking and disk overhead

First create a temporary table of type HEAP with the same structure as your target table, then insert into it: This will be much faster, as no locking and disk IO happens. Then run

INSERT INTO final_table SELECT * FROM temporary_table

If mitigation doesn't suffice, you will need to consider using non-parameterized queries for this use case. The usual caveats apply.

Upvotes: 1

manuskc
manuskc

Reputation: 794

Might not be the best solution, But you can try constructing a query string like INSERT INTO [table] VALUES (r1c1,r1c2,r1c3),(r2c1,r2c2,r2c3) ... and execute one mysql_query (or say one query for few hundred rows), you might even verify data programatically while constructing the sql query if it is not from a trusted source.

Upvotes: 1

Related Questions