Eloquent Performance: N+1 Query Problems

Eloquent performance is typically the main reason for slow Laravel projects. A big part of that is a so-called "N+1 Query Problem". In this article, I will show a few different examples of what to watch out for, including the cases when the problem is "hidden" in unexpected places in the code.

What is the N+1 Query Problem

In short, it's when Laravel code runs too many database queries. It happens because Eloquent allows developers to write a readable syntax with models, without digging deeper into what "magic" is happening under the hood.

This is not only an Eloquent, or even Laravel, problem: it's well-known in the dev industry. Why is it called "N+1"? Because, in the Eloquent case, it queries ONE row from the database, and then performs one more query for EACH related record. So, N queries, plus the record itself, total N+1.

To solve it, we need to query the related records upfront, and Eloquent allows us to do that easily, with so-called eager loading. But before we get to the solutions, let's discuss the problems

Case. "Regular" N+1 Query. This one can be taken directly from the official Laravel documentation:

// app/Models/Book.php:
class Book extends Model
{
    public function author()
    {
        return $this->belongsTo(Author::class);
    }
}
 
// Then, in some Controller:
$books = Book::all();
 
foreach ($books as $book) {
    echo $book->author->name;
}

What happens here? The $book->author part will perform one extra DB query for every book, to get its author.

Look at the number of queries.

As you can see, for 20 books, there are 21 queries, exactly N+1, where N = 20.

And yes, you get it right: if you have 100 books on the list, you will have 101 queries to the DB. Awful performance, although the code seemed "innocent", right.

The fix is to load the relationship upfront, immediately in the Controller, with the eager loading that I mentioned earlier:

// Instead of:
$books = Book::all();
 
// You should do:
$books = Book::with('author')->get();

The result is much better - only 2 queries:

When you use eager loading, Eloquent gets all the records into the array and launches ONE query to the related DB table, passing those IDs from that array. And then, whenever you call $book->author, it loads the result from the variable that is already in memory, no need to query the database again.