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

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.

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.

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

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;
                break;
            }
            $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) {
            $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 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.