How can splitting insert statements into multiple blocks help with inserting large amounts of data in PHP?

When inserting large amounts of data in PHP using insert statements, it can be more efficient to split the insert statements into multiple blocks rather than trying to insert all the data at once. This can help prevent memory issues and improve performance by allowing the database to process smaller batches of data at a time. 

// Sample code for splitting insert statements into multiple blocks
$pdo = new PDO("mysql:host=localhost;dbname=mydatabase", "username", "password");

$data = array(/* array of data to insert */);

$chunkSize = 1000; // Number of records to insert at once

for ($i = 0; $i < count($data); $i += $chunkSize) {
    $chunk = array_slice($data, $i, $chunkSize);
    
    $placeholders = rtrim(str_repeat('?,', count($chunk)), ',');
    $stmt = $pdo->prepare("INSERT INTO mytable (column1, column2) VALUES $placeholders");

    $stmt->execute($chunk);
}

Keywords

PHP insert statements large data performance optimization batch processing

Related Questions