PHP MySQL INSERT 1-3,000 rows as fast and efficient as possible

I'm looking for a quick way of INSERT

1-3,000 rows to a MySQL database using PHP. My current solution takes about 42 seconds to insert lines, which I think could be much faster.

I am using a self-writing DB class, the method insert()

takes two parameters (string) $table

and (array) $vars

. Array $items

is an associative array, where key is the name of a column in the table and value is the value to insert. This works really well because I sometimes have 30 columns in the table and already have the data in the array. Below is the method insert()


    function insert($table,$vars) {
        $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 keys and values โ€‹โ€‹from $ vars and create an INSERT statement. It works, I think the problem I'm having is sending requests 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 everything in one go? If so, can this handle 3000 insert statements in a single call?

Is there any other way that I am not looking at? Any information is appreciated.



source to share

6 answers

The most efficient way is to use the multiple line insert syntax:

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


Manual :

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

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

The list of values โ€‹โ€‹for each line must be enclosed in parentheses.



Two ways to improve insert speed:

  • In the beginning, before any INSERT

    , follow mysql_query("START TRANSACTION");

    or simpler mysql_query("BEGIN");

    . Finally, do a mysql_query("COMMIT");

    . These two lines accelerate volumetric insertion by 5-10 times throughput.

  • If the backend of the table is MyISAM

    (NOT InnoDB), do INSERT

    with word DELAYED

    . For example, instead INSERT INTO table


    to speed up with an addition of 10-15 times.

If you combine the two methods, it is possible to achieve a speedup of 100 times.



Mysql can import data directly from a file, which can greatly speed up data import. Cm:




As usual, it depends; you don't even mention which engine you are using, which is a big determinant. But I found the MySQL manual to be very reliable.



Automatic detection of the maximum number of inserts.

to insert such quantities (3000), there should be no problem doing something like (if you are using pdo):

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


You can improve on this to create a general way to create large operators like the ones above for tables with different numbers 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 won't work with any combination of rows and number of fields.

It seems like mysql is not limited to just the number of rows, but the number of parameters is also taken into account.

But you don't want to change your code whenever a new release of mysql changes the parameter limit, row limit, or even the sql clause size.

So, a much better approach to creating a generic way to generate large statements would be to try to spoof the subclass 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;
            $ammountInserts = $size;
            $size *= 2;
        } catch(\Exception $e) {

    } while($previousSize != $size);

    return $return;


One thing you should keep in mind is that since you don't know what constraints can be requested, fewer items can be clicked, all you need to insert.

Thus, you will need to create a strategy like the one below to successfully insert them into 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) {
            $i = 0;
            $values = array();
        $values[] = "value1" . $j;
        $values[] = "value2" . $j;
    if ($i > 0) {
        $insertRemaining = Db::getBiggestInsertStatement($table, array('field1', 'field2'), $i, $maximumInserts);


I tried to insert 1,000,000 rows into a single column table and it did it in seconds, again in minutes to insert them one at a time.



A standard technique for speeding up bulk inserts is to use a prepared SQL statement inside a loop within a transaction. This will make it quite optimal. After that, you can try to customize it in various ways, but you are probably wasting your time.



All Articles