The fastest safe way to insert many rows in PHP is a single INSERT with multiple value groups, using a prepared statement with one placeholder per value, inside a transaction. A loop that executes one insert per row pays a network round trip for every single row.
The naive loop is not wrong — it produces correct data. It is just paying a fixed cost per row that it does not need to pay, and at a thousand rows that cost dominates everything else. There are three techniques that matter, and they compose.
Table of contents
- The loop, and why it is slow
- The multi-row insert
- Duplicates, and what to do about them
- Never build the SQL by concatenation
- When the batch is genuinely large
- How this fits the rest of the stack
- FAQ
The loop, and why it is slow
<?php
// Correct, and slower than it needs to be
$stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (?, ?)');
foreach ($users as $user) {
$stmt->execute([$user['name'], $user['email']]);
}This is at least prepared once and executed many times, which avoids re-parsing the SQL. What it does not avoid is the round trip: each execute sends a message to the database and waits for a response. With a millisecond of latency, a thousand rows costs a second in waiting alone, before the database does any work.
There is a second, larger cost that is easy to miss. Without an explicit transaction, most engines commit each insert separately, which means a disk flush per row. That is usually the dominant cost, and it is also a correctness problem — a failure halfway through leaves half the rows committed.
Wrapping the same loop in a transaction is a one-line change with a disproportionate effect:
<?php
$pdo->beginTransaction();
try {
$stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (?, ?)');
foreach ($users as $user) {
$stmt->execute([$user['name'], $user['email']]);
}
$pdo->commit();
} catch (Throwable $e) {
$pdo->rollBack();
throw $e;
}Now it is one commit instead of a thousand, and the operation is all-or-nothing. If you change only one thing, change this.
The multi-row insert
SQL allows several value groups in one statement, which collapses the round trips as well as the commits:
<?php
function insertUsers(PDO $pdo, array $users): void
{
if ($users === []) {
return;
}
$placeholders = implode(',', array_fill(0, count($users), '(?, ?)'));
$sql = "INSERT INTO users (name, email) VALUES $placeholders";
$values = [];
foreach ($users as $user) {
$values[] = $user['name'];
$values[] = $user['email'];
}
$pdo->prepare($sql)->execute($values);
}The placeholder count is derived from the data, so the statement is still fully parameterised — no value is ever concatenated into the SQL. That is what keeps this safe.
Do not build one enormous statement. Databases limit packet size and prepared-statement parameter counts, and a very large statement can fail outright or consume a lot of memory on both sides. Chunk it:
<?php
$pdo->beginTransaction();
try {
foreach (array_chunk($users, 500) as $chunk) {
insertUsers($pdo, $chunk);
}
$pdo->commit();
} catch (Throwable $e) {
$pdo->rollBack();
throw $e;
}Chunks of a few hundred to a thousand rows capture nearly all of the available speedup. Beyond that the returns flatten and the failure modes get worse, so there is no reason to push it.
The mysqli equivalent works the same way, using variadic argument unpacking to bind a dynamic number of parameters:
<?php
$values = [1, 2, 3, 4];
$stmt = $mysqli->prepare('INSERT INTO test(id) VALUES (?), (?), (?), (?)');
$stmt->bind_param('iiii', ...$values);
$stmt->execute();Duplicates, and what to do about them
A batch insert that hits a unique constraint fails, and by default the whole statement fails. There are three standard responses and they are not interchangeable.
<?php
// 1. Update the existing row (MySQL)
$sql = "INSERT INTO users (email, name, updated_at)
VALUES $placeholders
ON DUPLICATE KEY UPDATE
name = VALUES(name),
updated_at = VALUES(updated_at)";
// 2. Skip conflicting rows (Postgres)
$sql = "INSERT INTO users (email, name)
VALUES $placeholders
ON CONFLICT (email) DO NOTHING";
// 3. Upsert (Postgres)
$sql = "INSERT INTO users (email, name)
VALUES $placeholders
ON CONFLICT (email) DO UPDATE
SET name = EXCLUDED.name";Avoid MySQL’s INSERT IGNORE for this. It suppresses more than duplicate-key errors — truncated data, type conversion failures and other warnings are silently downgraded too, so rows get inserted with values you did not intend and nothing tells you.
The rule of thumb: ON DUPLICATE KEY UPDATE or ON CONFLICT DO UPDATE when the incoming data should win, DO NOTHING when the existing row should. Decide deliberately, because the difference only shows up in production data.
Never build the SQL by concatenation
The version that appears in far too many codebases:
<?php
// SQL injection, and it will happen
$rows = [];
foreach ($users as $user) {
$rows[] = "('{$user['name']}', '{$user['email']}')";
}
$pdo->query('INSERT INTO users (name, email) VALUES ' . implode(',', $rows));One name containing an apostrophe breaks the query. One name containing a crafted string does something worse. This is not a hypothetical risk — user-supplied text with quotes in it is ordinary, and imported CSV data is full of it.
The escaping functions are not the answer either. real_escape_string depends on the connection character set being set correctly, and there are documented ways to defeat it when it is not. Prepared statements do not have that failure mode, because values never pass through the SQL parser at all.
Two settings worth having on every connection, so failures are loud rather than silent:
<?php
$pdo = new PDO($dsn, $user, $pass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => false,
]);ERRMODE_EXCEPTION turns a failed query into an exception instead of a false return value nobody checks. Disabling emulated prepares means the statement is genuinely prepared server-side, so parameters are sent separately from the SQL rather than being interpolated by the driver.
When the batch is genuinely large
Above roughly a hundred thousand rows, even batched inserts stop being the right tool. Two better options:
Bulk load from a file. Both MySQL and Postgres have a dedicated path that bypasses much of the per-row overhead and is dramatically faster than any INSERT-based approach.
<?php
// MySQL
$pdo->exec("LOAD DATA LOCAL INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '\"'
IGNORE 1 LINES (name, email)");
// Postgres, via the COPY protocol
$pdo->pgsqlCopyFromFile('users', '/tmp/users.csv', ',');Drop the indexes first. Every index is maintained on every insert. For a large one-off load into a table you control, dropping non-essential indexes, loading, and rebuilding them afterwards is often faster than loading with them in place — though only do this where the table is not being read concurrently.
And the practical point that outranks all of it: a large import should not run inside a web request. A request that takes ninety seconds hits a timeout somewhere — PHP’s own limit, the web server’s, or a proxy’s — and leaves the import in an unknown state. Move it to a queue and a worker process, and give the user a job to poll.
How this fits the rest of the stack
The performance story here is mostly about round trips and commits, and the correctness story is mostly about transactions and parameterisation. Both point at the same place: a bulk import is a background job, not a web request.
Running it that way needs a process that can take as long as it takes, and a database that will not fall over while it does. On RunxBuild, a PHP or any other service deploys from your GitHub repository as a long-running process with a live route, build and runtime logs in one place, persistent storage for the file being imported, and autoscaling between a floor and ceiling plan you choose so a heavy import does not starve normal traffic. Managed MySQL and Postgres sit alongside on private networking, with connection limits and backups you did not have to script. To see what a service, its database and storage add up to, the RunxBuild hosting calculator lists them as separate line items.
Useful related references:
- PHP Show All Errors: Why the Page Is Blank and How to Fix It
- PHP Composer: Dependency Management, and the Two Files That Matter
- PHP Warnings: What Changed in PHP 8 and Where They Should Go
- Services on RunxBuild
FAQ
What is the fastest way to insert many rows in PHP?
A single INSERT statement with multiple value groups, prepared with one placeholder per value, inside a transaction — chunked into batches of a few hundred to a thousand rows. That collapses both the per-row network round trip and the per-row commit, which are the two costs that dominate a naive loop.
Do I need a transaction for bulk inserts?
Yes, and it is the highest-value single change. Without one, most engines commit each insert separately, which means a disk flush per row. It is also a correctness issue: a failure partway through a loop without a transaction leaves some rows committed and some not.
How many rows should I put in one batch?
Between a few hundred and a thousand. Databases limit packet size and prepared-statement parameter counts, so a single enormous statement can fail or consume a lot of memory on both sides. Most of the available speedup is captured well before those limits.
How do I handle duplicate keys in a batch insert?
Use ON DUPLICATE KEY UPDATE in MySQL or ON CONFLICT in Postgres, choosing DO UPDATE when incoming data should win and DO NOTHING when the existing row should. Avoid MySQL’s INSERT IGNORE, which also suppresses truncation and conversion warnings, silently storing values you did not intend.
Is it safe to build the VALUES clause as a string?
Only the placeholders, never the values. Generating (?, ?) groups from the row count is fine, because the data still travels as bound parameters. Concatenating actual values into the SQL is an injection vulnerability and breaks on any name containing an apostrophe. Disable emulated prepares so parameters are genuinely sent separately.