Inserting Data from a Large CSV in Laravel Over 10x Faster
Tags: laravel, mysql, mariadb
Published: 2024-03-24 18:57:46

Typically, when inserting data from a CSV, you might read the file one line at a time and do batch inserts. Something like this:
```php
$file = fopen(storage_path('app/test.csv'), 'r');

$i = 0;
$batch = [];
$batchSize = 1000;
while ($line = fgetcsv($file)) {
   if ($i === 0) {
       $i++;
       continue;
   }
   $batch[] = [
       'column' => $line[1],
       // etc...
    ];

    $i++;

    if (($i % $batchSize) === 0) {
        Model::insert($batch);
        $batch = [];
    }

}

// Insert the remaining batch
if (! empty($batch)) {
    Model::insert($batch);
}
```
This approach is great if you're dealing with a CSV that has hundreds or even thousands of rows. But what if the CSV has millions of rows? Enter MySQL's [LOAD DATA statement](https://dev.mysql.com/doc/refman/8.0/en/load-data.html).

The `LOAD DATA` statement is MySQL's lightning fast, built-in way of loading CSVs into a table in your database. With it, you can specify a custom separator or line ending or even transform the data before it's saved to your table and much more!

So how does it compare to the approach above? Let's see!

# Benchmarking

## Setup

I have a `people` table with `first_name`, `last_name`, `email`, `phone`, `street_address`, `city`, `state`, `postal_code`, `country` and `date_of_birth` columns. My 1.5 GB CSV has 10 million rows with similar columns - `ID`, `First Name`, `Last Name`, `Email`, `Phone`, `Street Address`, `City`, `State`, `Postal Code`, `Country` and `Date of Birth`. The Date of Birth field is in MM/DD/YYYY format so we have to convert it to a DB friendly format.

**Computer:** M1 Pro MacBook Pro
**PHP:** 8.3
**Laravel:** 11
**MySQL/MariaDB:** MariaDB 11.2.2

## Batched Inserts

This will look very similar to the approach above. The code:
```php
// Disable unique checks and foreign key checks to speed up inserts
DB::statement('SET @@session.unique_checks = 0');
DB::statement('SET @@session.foreign_key_checks = 0');

$file = fopen(storage_path('app/test.csv'), 'r');

$i = 0;
$batch = [];
$batchSize = 1000;
while ($line = fgetcsv($file)) {
    if ($i === 0) {
        $i++;

        continue;
    }

    $batch[] = [
        'first_name' => $line[1],
        'last_name' => $line[2],
        'email' => $line[3],
        'phone' => $line[4],
        'street_address' => $line[5],
        'city' => $line[6],
        'state' => $line[7],
        'postal_code' => $line[8],
        'country' => $line[9],
        'date_of_birth' => Carbon::parse($line[10])->format('Y-m-d'),
    ];

    $i++;

    if (($i % $batchSize) === 0) {
        Person::insert($batch);
        $batch = [];
    }

}

// Insert the remaining batch
if (! empty($batch)) {
    Person::insert($batch);
}
```
**This takes an average of 6 minutes.**

## LOAD DATA Statement

To write the statement, I'm using a package I wrote - [isaacdew/load-data](https://github.com/isaacdew/load-data).

The code:
```php
LoadData::from(storage_path('app/test.csv'))
    ->to(Person::class)
    ->fieldsTerminatedBy(',')
    ->fieldsEnclosedBy('"', true)
    ->useFileHeaderForColumns()
    ->onlyLoadColumns([
        'first_name',
        'last_name',
        'email',
        'phone',
        'street_address',
        'city',
        'state',
        'postal_code',
        'country',
        'date_of_birth',
    ])
    ->setColumn('date_of_birth', "STR_TO_DATE(@date_of_birth, '%c/%d/%Y')")
    ->load();
```
**This takes an average of 0.53 minutes.**

To run the benchmarks yourself, see [the repository I setup](https://github.com/isaacdew/load-data-benchmarks).

# Conclusion

MySQL's `LOAD DATA` statement is incredibly fast and flexible. If you need to load large CSVs into your database, I highly recommend it. You can install my package using composer to make writing your load statements in Laravel easy - `composer require isaacdew/load-data`