Benchmarking One Million MySQL Inserts in Laravel

You've probably read that you should use insert() instead of create() for bulk operations. But how much does it actually matter at scale? And where do the real bottlenecks hide when you push past 100k, 500k, and a million rows?

This article runs a structured comparison of four insert strategies in Laravel 12 using MySQL, measures wall-clock time and peak memory, then walks through the configuration changes that have the biggest impact. No hand-wavy percentages — just reproducible benchmark code you can adapt.

Prerequisites

• Laravel 12.x (PHP 8.2+; PHP 8.4 recommended for best JIT gains)
• MySQL 8.0+ or MariaDB 10.6+
• A local or staging server with consistent load (do not benchmark on shared hosting)
• php.ini memory_limit of at least 512M for the naive approaches

For context on how database performance fits into the broader Laravel stack (Octane, caching, Livewire), see the Laravel Performance Guide: Databases, Livewire, Octane, and Caching.

---

The Test Table

Keep the schema representative but simple. A real-world events table with an index is more honest than a two-column toy table.

// database/migrations/2026_08_01_000000_create_events_table.php
Schema::create('events', function (Blueprint $table) {
    $table->id();
    $table->string('name', 100);
    $table->string('category', 50)->index();
    $table->unsignedBigInteger('user_id')->index();
    $table->json('payload')->nullable();
    $table->timestamp('occurred_at');
    $table->timestamps();
});

One secondary index on category and one on user_id is enough to make MySQL do real work during inserts without being artificially punishing. The payload JSON column keeps the average row size realistic for production event-tracking tables.

---

Strategy 1 — Eloquent create() in a Loop (Baseline)

This is the pattern every Laravel beginner writes first. It fires one INSERT statement per row and instantiates a full Eloquent model for each.

// app/Console/Commands/BenchmarkInserts.php (excerpt)
public function strategyEloquentLoop(int $total): void
{
    for ($i = 0; $i < $total; $i++) {
        Event::create([
            'name'        => 'event_' . $i,
            'category'    => $this->randomCategory(),
            'user_id'     => rand(1, 10000),
            'payload'     => json_encode(['seq' => $i]),
            'occurred_at' => now(),
        ]);
    }
}

At 1,000 rows this is fine. At 100,000 rows the overhead of model instantiation, mutator resolution, event dispatching (creating, created), and individual round-trips to MySQL becomes the bottleneck. At one million rows, expect this to take 10–20 minutes on a typical VPS and exhaust default memory limits. The per-row network latency alone — even on a localhost socket — adds up to minutes when multiplied by a million iterations.

---

Strategy 2 — Query Builder insert() in Chunks

Skipping Eloquent and batching rows into chunks reduces both round-trips and PHP memory pressure. Instead of one INSERT per row, you fire one INSERT per chunk covering hundreds of rows at once.

public function strategyBuilderChunked(int $total, int $chunkSize = 500): void
{
    $chunk = [];

    for ($i = 0; $i < $total; $i++) {
        $chunk[] = [
            'name'        => 'event_' . $i,
            'category'    => $this->randomCategory(),
            'user_id'     => rand(1, 10000),
            'payload'     => json_encode(['seq' => $i]),
            'occurred_at' => now()->toDateTimeString(),
            'created_at'  => now()->toDateTimeString(),
            'updated_at'  => now()->toDateTimeString(),
        ];

        if (count($chunk) === $chunkSize) {
            DB::table('events')->insert($chunk);
            $chunk = [];
        }
    }

    // flush remainder
    if ($chunk) {
        DB::table('events')->insert($chunk);
    }
}

Chunk size matters. MySQL's max_allowed_packet (default 64 MB in MySQL 8) limits how large a single INSERT ... VALUES (...) statement can be. With JSON payloads, 500 rows per chunk is a safe starting point. At 1,000 rows per chunk you may need to raise max_allowed_packet to 128 MB in my.cnf.

---

Strategy 3 — Wrapping Chunks in Transactions

MySQL writes every committed statement to the binary log and flushes InnoDB's redo log unless you disable innodb_flush_log_at_trx_commit. Wrapping multiple inserts in a single transaction dramatically reduces the number of fsync calls to disk — and disk I/O is almost always the dominant cost when inserting at scale.

public function strategyTransactional(int $total, int $chunkSize = 500): void
{
    $chunk    = [];
    $batchSize  = 10000; // rows per transaction
    $batchCount = 0;

    DB::beginTransaction();

    for ($i = 0; $i < $total; $i++) {
        $chunk[] = $this->makeRow($i);
        $batchCount++;

        if (count($chunk) === $chunkSize) {
            DB::table('events')->insert($chunk);
            $chunk = [];
        }

        if ($batchCount === $batchSize) {
            DB::commit();
            DB::beginTransaction();
            $batchCount = 0;
        }
    }

    if ($chunk) {
        DB::table('events')->insert($chunk);
    }

    DB::commit();
}

Keep transactions under ~10,000–20,000 rows. Extremely large transactions inflate the InnoDB undo log, consuming disk space and slowing rollback if something goes wrong mid-import. Rolling back a 1M-row transaction can take as long as the original insert.

---

Strategy 4 — LOAD DATA INFILE via a Temp CSV

For the absolute fastest path, MySQL's LOAD DATA INFILE bypasses the SQL parser entirely. Laravel doesn't provide a built-in wrapper, but PDO does. The engine reads a plain-text CSV, maps columns directly to table fields, and skips most of the row-validation overhead of the SQL INSERT path.

public function strategyLoadDataInfile(int $total): void
{
    $tmpFile = tempnam(sys_get_temp_dir(), 'events_') . '.csv';
    $handle  = fopen($tmpFile, 'w');

    for ($i = 0; $i < $total; $i++) {
        fputcsv($handle, [
            'event_' . $i,
            $this->randomCategory(),
            rand(1, 10000),
            json_encode(['seq' => $i]),
            now()->toDateTimeString(),
            now()->toDateTimeString(),
            now()->toDateTimeString(),
        ]);
    }
    fclose($handle);

    DB::statement("
        LOAD DATA LOCAL INFILE '{$tmpFile}'
        INTO TABLE events
        FIELDS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '\"'
        LINES TERMINATED BY '\n'
        (name, category, user_id, payload, occurred_at, created_at, updated_at)
    ");

    unlink($tmpFile);
}

Requirements: The MySQL user needs FILE privilege. Enable local_infile=1 in both my.cnf and the PDO DSN options:

// config/database.php
'options' => [
    PDO::MYSQL_ATTR_LOCAL_INFILE => true,
],

Note on security: LOAD DATA LOCAL INFILE is disabled by default in MySQL 8.0.22+ for security reasons. Only enable it on trusted, internal database connections. Never expose the temp file path to user input.

---

Building the Artisan Benchmark Command

Wrap all four strategies in a single Artisan command so you can run them back-to-back with consistent timing. Using microtime(true) and memory_get_peak_usage() gives you wall-clock time and peak PHP memory in one pass.

// app/Console/Commands/BenchmarkInserts.php
public function handle(): void
{
    $total    = (int) $this->option('rows');    // default: 1000000
    $strategy = $this->option('strategy');      // eloquent|chunked|transactional|infile

    $this->info("Running strategy: {$strategy} for {$total} rows");

    DB::table('events')->truncate();

    $start = microtime(true);

    match ($strategy) {
        'eloquent'      => $this->strategyEloquentLoop($total),
        'chunked'       => $this->strategyBuilderChunked($total),
        'transactional' => $this->strategyTransactional($total),
        'infile'        => $this->strategyLoadDataInfile($total),
    };

    $elapsed = round(microtime(true) - $start, 2);
    $memory  = round(memory_get_peak_usage(true) / 1024 / 1024, 1);
    $count   = DB::table('events')->count();

    $this->table(
        ['Strategy', 'Rows', 'Time (s)', 'Peak Memory (MB)'],
        [[$strategy, $count, $elapsed, $memory]]
    );
}

Run each strategy at 10,000 rows first to verify correctness, then scale to 100,000, 500,000, and finally 1,000,000. On your specific server and MySQL version, the crossover points between strategies will differ from the indicative numbers below.

---

Benchmark Results (Indicative)

The table below reflects typical relative timings on a mid-range server (4 vCPU, 8 GB RAM, SSD, MySQL 8.0, PHP 8.4, InnoDB). Your numbers will differ based on hardware, index count, and row size. Run these yourself to get accurate baselines for your environment.

Strategy	1M Rows (approx.)	Peak Memory
Eloquent create() loop	12–20 min	600 MB+
Builder insert() chunks (500)	3–5 min	~80 MB
Builder + transactions (10k/tx)	60–90 sec	~80 MB
LOAD DATA INFILE	10–20 sec	~40 MB

The gap between the Eloquent loop and chunked inserts is almost entirely model overhead and round-trip count. The gap between chunked inserts and transactional inserts is almost entirely fsync reduction. The jump to LOAD DATA INFILE eliminates the SQL parser overhead entirely.

---

MySQL Configuration Levers

Before assuming your code is the bottleneck, check these MySQL settings. Application-level changes can only get you so far if the database engine is configured for safe OLTP writes rather than bulk throughput.

# /etc/mysql/mysql.conf.d/mysqld.cnf

# Reduce fsync frequency (0 = no flush per commit, risky; 2 = flush per second)
innodb_flush_log_at_trx_commit = 2

# Larger buffer pool = fewer disk reads during index maintenance
innodb_buffer_pool_size = 4G   # ~70% of available RAM on dedicated DB servers

# Larger redo log size reduces checkpoint frequency during bulk inserts
innodb_redo_log_capacity = 2G  # MySQL 8.0.30+ syntax

# Permit large multi-row INSERT payloads
max_allowed_packet = 128M

# Increase sort buffer for reindex operations after bulk load
sort_buffer_size = 8M

---

Tradeoffs at a Glance

• Eloquent vs Query Builder: Eloquent is safer (observers, casting, events) but roughly 10–15x slower at bulk scale. Use the Query Builder for import jobs and keep Eloquent for business logic where model events matter.
• Chunked vs transactional: Transactions are faster due to fewer fsyncs but hold locks longer. For multi-tenant systems with concurrent writers, prefer smaller transaction windows to avoid blocking reads.
• LOAD DATA INFILE vs SQL INSERT: Fastest by a wide margin but requires file-system access to the database server, adds CSV serialization complexity, and is disabled by default in hardened MySQL setups. Use it for one-time migrations; avoid it for application-runtime inserts.
• innodb_flush_log_at_trx_commit = 2 vs 1: Setting 2 is safe for most batch-import scenarios where losing one second of data is acceptable. Always revert to 1 for production OLTP workloads.
• Disabling secondary indexes vs live updates: Temporarily disabling indexes during a one-time load and rebuilding them afterward is faster overall, but the table is partially unusable during the load window. Acceptable for maintenance windows, not for live traffic.

---

Understanding MySQL insert performance at scale is one layer of Laravel's broader performance story. For Octane throughput, N+1 elimination with automatic eager loading (Laravel 12.8+), and the new memoized cache driver (12.9+), the Laravel Performance Guide: Databases, Livewire, Octane, and Caching covers each area in depth.

If you need Laravel development in Mumbai, Mumbai Web Designer builds production-grade Laravel applications.