Faster Eloquent Chunking
Tags: laravel, php
Published: 2019-09-11 21:51:35

As your applications scale, processing large amounts of database records with Laravel Eloquent can become increasingly difficult. Resulting in out of memory exceptions and overall slowing down your application. Why is that?

When fetching results from the database you are in turn pulling that data into memory. Take this snippet of code for instance
```php
Post::all()->each(function ($post) {
    // ...
});
```
Which results in the following query, loading all records in the posts table into memory
```sql
select * from posts;
```
Typically for tables with a small number of records, this is absolutely acceptable. However, as you accumulate tens of thousands of posts you will eventually begin hitting memory resource constraints of your webserver.

### Chunking
A common approach in Laravel is to use Eloquent's (via [BuildsQuery](https://laravel.com/api/6.x/Illuminate/Database/Concerns/BuildsQueries.html)) [`chunk()`](https://laravel.com/api/6.x/Illuminate/Database/Concerns/BuildsQueries.html#method_chunk) method which fetches a fixed amount of records breaking a larger set into more consumable chunks.
```php
Post::chunk(1000, function ($post) {
    // ...
});
```

While this might seem fine there are both improvements and gotchas to be aware of.

First, imagine the following scenario: you are fetching Post records from the database to update an attribute that you are also using in a where clause
```php
Post::where('published_at', '<', now())->chunk(1000, function ($post) {
    $post->update('published_at', now());
});
```
Although contrived, this exemplifies a very real problem where such a query would result in an endless loop as the `published_at` attribute will always be less than `now()` at the time of the queries next execution (assuming accuracy to the second as with MySQL's `timestamp` column type or similar).

Second, there is the matter of the queries performance and its impact on the database server. The above code would result in a query similar to the following
```sql
select * from posts order by posts.id asc limit 1000 offset 9000
```

MySQL is unable to go directly to the offset due to deleted records and additional query constraints and, therefore, this query has to effectively select the first 10,000 records to return only the last 1,000 selected. As you can imagine this will not scale well into many-thousands or even millions of rows. This will cause your database server to use unnecessary resources slowing down all other queries from your application.

### Chunking... but better!
In order to both prevent the unforeseen gotcha and improve database server performance we can use Eloquent's [chunkById](https://laravel.com/api/6.x/Illuminate/Database/Concerns/BuildsQueries.html#method_chunkById) method
```php
Post::where('published_at', '<', now())->chunkById(1000, function ($post) {
    $post->update('published_at', now());
});
```

The above code snippet will result in a query similar to the following
```sql
select * from posts where published_at < '2019-09-11 12:00:00' and id > 9000 order by id asc limit 1000
```

Why is this approach considered "better"?

a) it allows MySQL to completely skip the first 9000 (assumingly sequential) records
b) we will no longer be re-selecting records which we have already updated due to the id constraint in our where clause

### Bonus - How?! 🤔
Diving into the `chunkyById` method of the `BuildsQueries` trait we [see that](https://github.com/laravel/framework/blob/6.x/src/Illuminate/Database/Concerns/BuildsQueries.php#L92-L107) the id of the last record fetched (remember, we're ordering by id in ascending order) is stored and used as a parameter in the next query to be run.