Mastering High-Performance CSV Imports: From Collections to MySQL Native Loaders

Importing data is a deceptive task. It starts simple: read a file, loop through the rows, and save them to a database. But as the row count climbs from hundreds to millions, the "simple" way becomes a catastrophic failure. Memory limits are breached, database connections timeout, and processing times stretch from seconds into hours. This guide explores how to scale a Laravel import from a basic collection-based approach to high-performance methods using PDO and MySQL native features.

We will operate under three strict constraints to simulate a real-world production environment: a memory limit of 256MB, a refusal to use background queues/jobs for this specific exercise, and a focus on vanilla PHP and Laravel tools without external packages. The goal is to reach a sub-20-second import for one million records.

Prerequisites

Before diving in, you should have a solid grasp of the following:

• PHP 8.x: Familiarity with closures, arrays, and file handles.
• Laravel 11: Basic understanding of Eloquent and the Laravel.
• MySQL: Knowledge of tables and basic SQL syntax.
• CLI: Comfort running Laravel commands.

Key Libraries & Tools

• Laravel: Our primary environment, specifically using the new Concurrency facade.
• PDO: Used for low-level database interactions to bypass Eloquent overhead.
• MySQL: The target database engine.
• fgetcsv(): A native PHP function that parses a line from a file pointer and checks for CSV fields.

Code Walkthrough: Evolution of an Import

Phase 1: The Collection Pitfall

The most intuitive Laravel approach is loading a file into a collection. While readable, it is the least efficient.

$rows = file($filePath);
$data = collect($rows)->skip(1)->map(function ($line) {
    $row = str_getcsv($line);
    return [
        'name' => $row[1],
        'email' => $row[2],
        // ... more fields
    ];
});

foreach ($data as $customer) {
    Customer::create($customer);
}

Why it fails: The file() function reads the entire file into memory at once. At 100,000 rows, you hit the 256MB limit. Furthermore, Customer::create() triggers a separate SQL INSERT for every row, making it incredibly slow.

Phase 2: Streamed File Pointers and PDO Chunks

To scale, we must stop loading the whole file. Instead, we use fopen() and fgetcsv() to stream the file line-by-line. To improve database performance, we move away from Eloquent and use PDO prepared statements with manual chunking.

$handle = fopen($filePath, 'r');
fgetcsv($handle); // Skip header

$pdo = DB::connection()->getPdo();
$stmt = $pdo->prepare("INSERT INTO customers (name, email, ...) VALUES (?, ?, ...)");

while (($row = fgetcsv($handle)) !== false) {
    $stmt->execute([$row[1], $row[2], ...]);
}
fclose($handle);

By executing 1,000,000 individual queries with a single prepared statement, the memory footprint drops to nearly zero. However, it still takes approximately 3 minutes. To go faster, we must bundle these into "multi-insert" statements.

Phase 3: Parallel Processing with Laravel Concurrency

Laravel introduced a Concurrency facade that allows us to run tasks in parallel. We can divide the file processing across 10 different worker processes. We use a Modulo Operator strategy to ensure each process only handles its assigned lines (e.g., Process 1 handles lines 0, 10, 20...).

Concurrency::run([
    function () use ($filePath, $i) {
        $handle = fopen($filePath, 'r');
        $currentLine = 0;
        while (($line = fgets($handle)) !== false) {
            if ($currentLine % 10 !== $i) {
                $currentLine++;
                continue;
            }
            // Process and insert the line here...
            $currentLine++;
        }
    }
], 10);

Performance Note: This method slashed the 1,000,000-row import time down to 4 seconds. Parallelization allows the CPU to utilize multiple cores to handle the parsing and I/O simultaneously.

Syntax Notes: The Power of fgetcsv vs str_getcsv

During high-volume processing, the choice of function matters. fgetcsv() is designed to work with file handles, making it perfect for streaming. On the other hand, str_getcsv() is better when you already have a string in memory (like when using fgets()). In the parallel approach, we use fgets() first to quickly skip rows we don't own, and only call str_getcsv() on the rows we intend to process. This saves significant CPU cycles.

Practical Examples

• E-commerce Migration: Moving millions of customer records or legacy orders from an old system to a new Laravel app.
• Financial Reporting: Importing daily transaction logs for analysis where speed is critical to meet processing windows.
• Data Warehousing: Seeding local development environments with massive datasets to test performance at scale.

Tips & Gotchas

• The PDO Placeholder Limit: MySQL via PDO has a limit on the number of placeholders (?) in a single query (usually 65,535). If you chunk 10,000 rows with 10 columns each, you will hit this limit. Always calculate your chunk size based on Limit / Number of Columns.
• MySQL local-infile: The absolute fastest method is the native LOAD DATA LOCAL INFILE command. However, this is often disabled for security. If you use it, you must enable it on both the PDO client and the MySQL server (SET GLOBAL local_infile = 1).
• Database Reconnection: When using the Concurrency facade, each process might need to explicitly reconnect to the database to avoid sharing a socket, which can cause intermittent connection errors.
• Date Formats: Databases are picky. Ensure you format your CSV dates (e.g., Y-m-d H:i:s) before inserting, or use SQL functions like NOW() for timestamps to reduce PHP memory usage.