CODEWITHSUNDEEP
phpmysqlperformance
Mike L.
Mike L.

Reputation: 1966

PHP MySQL INSERT 1-3,000 rows as quickly and efficently as possible

I am looking for the fastest way to INSERT 1-3,000 rows into a MySQL database using PHP. My current solution is taking around 42 seconds to insert the rows which I think that could be much faster.

I am using a self-written DB class, the insert() method takes two params (string) $table and (array) $vars. The $items array is an associative array where the key is the column name in the table and the value is the value to insert. This works really well for because I sometimes have 30 columns in a table and already have the data there in an array. The insert() method is below:

    function insert($table,$vars) {
        if(empty($this->sql_link)){
            $this->connection();
        }
        $cols = array();
        $vals = array();
        foreach($vars as $key => $value) {
            $cols[] = "`" . $key . "`";
            $vals[] = "'" . $this->esc($value) . "'";
        }
        //join the columns and values to insert into sql
        $fields = join(', ', $cols);
        $values = join(', ', $vals);

        $insert = mysql_query("INSERT INTO `$table` ($fields) VALUES ($values);", $this->sql_link);
        return $insert;
}

It should be self-explanatory but basically I take the keys and values from $vars and create an INSERT statement. It works, I think the problem I am having is sending the queries one at a time.

Should I build a long query string?

INSERT INTO table (field, field2, etc) VALUES (1, 2, ect);INSERT INTO table (field, field2, etc) VALUES (1, 2, ect);INSERT INTO table (field, field2, etc) VALUES (1, 2, ect);INSERT INTO table (field, field2, etc) VALUES (1, 2, ect);INSERT INTO table (field, field2, etc) VALUES (1, 2, ect); and send it all at one time? If so can this handle 3,000 insert statements in one call?

Is there another way I am not looking at? Any info is appreciated.

Thanks

Upvotes: 5

Views: 14200

Answers (7)

Shamim
Shamim

Reputation: 41

<?php
$data = "data/fullz.txt";
$db = new PDO("sqlite:db/ssninfo.db");
$db->beginTransaction();
$stmt = $db->prepare('INSERT INTO ssninfo (fname,lname,ssn,address,city,state,zip,phone,birth,email) VALUES (?,?,?,?,?,?,?,?,?,?)');
if($file=fopen($data, "r")){
    while(!feof($file)){
        $line = fgets($file);
        $part = explode('|', $line);
        $stmt->execute($part);
    }
}
$db->commit();

Upvotes: 1

Doug Amos
Doug Amos

Reputation: 4383

Mysql can import data directly from a file which can significantly speed up importing data. See:

LOAD DATA INFILE Syntax

Upvotes: 1

useless
useless

Reputation: 1906

Auto discovering the maximum ammount of inserts.

to insert that kind of ammounts (3000) there should not be any problem of doing something like (assuming you use pdo):

$stmt = $dbh->prepare("INSERT INTO yourtable(name, id) VALUES " . str_repeat('(?,?),', $amountOfRows - 1) . '(?,?)');

You can improve that to make create generic way to create big statements like the one above for tables with different ammount of fields:

$fields = array("name", "id");
$fieldList = implode(", ", $fields);
$params = '(' . str_repeat('?,', count($fields) - 1) . '?)';
$values = str_repeat($params . ',', $ammountOfRows - 1) .  $params;
$stmt = $dbh->prepare("INSERT INTO $table($fieldList) VALUES " . $values);

but the problem with the above solution is that wont work with any combination of rows and ammount of fields.

Seems to be that mysql is not only limited by the ammount of rows but also the ammount of parameters is taken into account.

But you dont want to be changing your code whenever a new mysql release changes the limit of the parameters, rows or even the size of the sql sentence.

So, a much better approach to create a generic way to generate big statements would be trying to feat the underlaying database engine:

/**
 * Creates an insert sql with the maximum allowed of parameters
 * @param string $table
 * @param string $attributeList
 * @param int &$ammountInserts returns the ammount of inserts
 * @return \PDOStatement
 */
public static function getBiggestInsertStatement($table, $attributeList, $max, &$ammountInserts)
{
    $previousSize = null;
    $size = 10;
    $sql = 'INSERT INTO ' . $table . '(' . implode(',', $attributeList) . ') values ';
    $return = null;
    $params = '(' . str_repeat('?,', count($attributeList) - 1) . '?)';

    do {
        try {
            $previousSize = $size;
            $values = str_repeat($params . ',', $size - 1) .  $params;
            $return = Db::getInstance()->prepare($sql . $values);
            if ($size > $max) {
                $values = str_repeat($params . ',', $max - 1) .  $params;
                $return = Db::getInstance()->prepare($sql . $values);
                $ammountInserts = $max;
                break;
            }
            $ammountInserts = $size;
            $size *= 2;
        } catch(\Exception $e) {

        }
    } while($previousSize != $size);

    return $return;
}

One thing that you must have in mind is that since you dont know that limits the query could be able to push a lower ammount of items that all that you need to insert.

So you would have to create a strategy like the one below to succesfuly achieve insert them all in any possible scenario:

    $insert = Db::getBiggestInsertStatement($table, array('field1','field2'), $numrows, $maximumInserts);
    $i = 0;
    $values = array();
    for ($j = 0; $j < $numrows; $j++) {
        if ($i === $maximumInserts) {
            $insert->execute($values);
            $i = 0;
            $values = array();
        }
        $values[] = "value1" . $j;
        $values[] = "value2" . $j;
        $i++;
    });
    if ($i > 0) {
        $insertRemaining = Db::getBiggestInsertStatement($table, array('field1', 'field2'), $i, $maximumInserts);
        $insertRemaining->execute($values);
    }

I have tried to insert in a table with a single column 1000000 rows, and it's done within seconds, agains minutes that would take to insert them one by one.

Upvotes: 0

f5inet
f5inet

Reputation: 71

Two ways of improve insertion speeds:

  1. At the start, before any INSERT, do a mysql_query("START TRANSACTION"); or a simpler mysql_query("BEGIN");. At the end, do a mysql_query("COMMIT");. These two lines, speeds up the bulk insertion a 5-10x performance.

  2. If the table backend is MyISAM (NOT InnoDB), do the INSERTs followed with the word DELAYED. For example, instead of INSERT INTO table use INSERT DELAYED INTO table for an aditional 10-15x speed-up.

If you combine the 2 methods, is posible to achieve a speed-up of 100 times.

Upvotes: 7

dkretz
dkretz

Reputation: 37645

As usual, it depends; you don't even mention which engine you're using, which is a big determinant. But I've found the MySQL manual guidance pretty reliable.

http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html

Upvotes: 0

Philip Sheard
Philip Sheard

Reputation: 5825

The standard technique for speeding up bulk inserts in to use a prepared SQL statement inside a loop inside a transaction. That will make it pretty well optimal. After that you could try tweaking it in various ways, but you are probably wasting your time.

Upvotes: -1

Interrobang
Interrobang

Reputation: 17434

The most performant way is to use the multiple-row insert syntax:

INSERT INTO table (field, field2, etc) VALUES (1, 2, etc),(1, 2, etc),(1, 2, etc);

Manual:

INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. Example:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

The values list for each row must be enclosed within parentheses.

Upvotes: 13

Related Questions