Optimize Laravel Performance: 4 Examples of N+1 Query Problems

Monday May 15, 2023

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. I will show you 4 different cases.

Case 1. "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.

I've created a small demo project to simulate this and seeded 20 fake books with their authors. Look at the number of queries.

Optimize Laravel Performance

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:

Optimize Laravel Performance

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.

Now, wait, you wonder what is this tool to show queries?

Always Use Debugbar. And Seed Fake Data.

This bottom bar is a package Laravel Debugbar. All you need to do to use it is install it:

composer require barryvdh/laravel-debugbar --dev

And that's it, it will show the bottom bar on all the pages. You just need to enable debugging with the .env variable APP_DEBUG=true, which is a default value for local environments.

Security Notice: make sure that when your project goes live, you have APP_DEBUG=false on that server, otherwise regular users of your website will see the debugbar and your database queries, which is a huge security issue.

Of course, I advise you to use Laravel Debugbar on all your projects. But this tool by itself will not show the obvious problems until you have more data on the pages. So, using Debugbar is only one part of the advice.

In addition, I also recommend having seeder classes that would generate some fake data. Preferably, a lot of data, so you would see how your project performs "in real life" if you imagine it growing successfully in the future months or years.

Use Factory classes and then generate 10,000+ records for books/authors and other models:

class BookSeeder extends Seeder
{
    public function run()
    {
        Book::factory(10000)->create();
    }
}

Then, browse through the website and look at what Debugbar shows you.

There are also other alternatives to Laravel Debugbar:

Case 2. Two Important Symbols.

Let's say that you have the same hasMany relationship between authors and books, and you need to list the authors with the number of books for each of them.

Controller code could be:

public function index()
{
    $authors = Author::with('books')->get();
 
    return view('authors.index', compact('authors'));
}

And then, in the Blade file, you do a foreach loop for the table:

@foreach($authors as $author)
    <tr>
        <td>{{ $author->name }}</td>
        <td>{{ $author->books()->count() }}</td>
    </tr>
@endforeach

Looks legit, right? And it works. But look at the Debugbar data below.

Optimize Laravel Performance

But wait, you would say that we are using eager loading, Author::with('books'), so why there are so many queries happening?

Because, in Blade, $author->books()->count() doesn't actually load that relationship from the memory.

$author->books() means the METHOD of relation
$author->books means the DATA eager loaded into memory

So, the method of relation would query the database for each author. But if you load the data, without () symbols, it will successfully use the eager loaded data:

Optimize Laravel Performance

So, watch out for what you're exactly using - the relationship method or the data.

Notice that in this particular example there's an even better solution. If you need only the calculated aggregated data of the relationship, without the full models, then you should load only the aggregates, like withCount:

// Controller:
$authors = Author::withCount('books')->get();
 
// Blade:
{{ $author->books_count }}

Optimize Laravel Performance

As a result, there will be only ONE query to the database, not even two queries. And also the memory will not be "polluted" with relationship data, so some RAM saved as well.

Case 3. "Hidden" Relationship in Accessor.

Let's take a similar example: a list of authors, with the column of whether the author is active: "Yes" or "No". That activity is defined by whether the author has at least one book, and it is calculated as an accessor inside of the Author model.

Controller code could be:

public function index()
{
    $authors = Author::all();
 
    return view('authors.index', compact('authors'));
}

Blade file:

@foreach($authors as $author)
    <tr>
        <td>{{ $author->name }}</td>
        <td>{{ $author->is_active ? 'Yes' : 'No' }}</td>
    </tr>
@endforeach

That "is_active" is defined in the Eloquent model:

use Illuminate\Database\Eloquent\Casts\Attribute;
 
class Author extends Model
{
    public function isActive(): Attribute
    {
        return Attribute::make(
            get: fn () => $this->books->count() > 0,
        );
    }
}

Notice: this is a new syntax of Laravel accessors, adopted in Laravel 9. You can also use the "older" syntax of defining the method getIsActiveAttribute(), it will also work in the latest Laravel version.

So, we have the list of authors loaded, and again, look what Debugbar shows:

Optimize Laravel Performance

Yes, we can solve it by eager loading the books in the Controller. But in this case, my overall advice is avoid using relationships in accessors. Because an accessor is usually used when showing the data, and in the future, someone else may use this accessor in some other Blade file, and you will not be in control of what that Controller looks like.

In other words, Accessor is supposed to be a reusable method for formatting data, so you're not in control of when/how it will be reused. In your current case, you may avoid the N+1 query, but in the future, someone else may not think about it.

Case 4. Be Careful with Packages.

Laravel has a great ecosystem of packages, but sometimes it's dangerous to use their features "blindly". You can run into unexpected N+1 queries if you're not careful.

Let me show you an example with a very popular spatie/laravel-medialibrary package. Don't get me wrong: the package itself is awesome and I don't want to show it as a flaw in the package, but rather as an example of how important it is to debug what is happening under the hood.

Laravel-medialibrary package uses polymorphic relationships between the "media" DB table and your model. In our case, it will be books that will be listed with their covers.

Book model:

use Spatie\MediaLibrary\HasMedia;
use Spatie\MediaLibrary\InteractsWithMedia;
 
class Book extends Model implements HasMedia
{
    use HasFactory, InteractsWithMedia;
 
    // ...
}

Controller code:

public function index()
{
    $books = Book::all();
 
    return view('books.index', compact('books'));
}

Blade code:

@foreach($books as $book)
    <tr>
        <td>
            {{ $book->title }}
        </td>
        <td>
            <img src="{{ $book->getFirstMediaUrl() }}" />
        </td>
    </tr>
@endforeach

That getFirstMediaUrl() method comes from the official documentation of the package.

Now, if we load the page and look at the Debugbar...'

Optimize Laravel Performance

20 books, 21 queries to the database. Precisely N+1 again.

So, the package does a bad job in performance? Well, no, because the official documentation is telling how to retrieve media files for one specific model object, for one book, but not for the list. That list part you need to figure out by yourself.

If we dig a bit deeper, in the trait InteractsWithMedia of the package, we find this relationship that is auto-included in all models:

public function media(): MorphMany
{
    return $this->morphMany(config('media-library.media_model'), 'model');
}

So, if we want all the media files to be eager loaded with the books, we need to add with() to our Controller:

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

This is the visual result, only 2 queries.

Optimize Laravel Performance

Again, this is the example not to show this package as a bad one, but with the advice that you need to check the DB queries at all times, whether they come from your code or an external package.

The Built-In Solution Against N+1 Query

Now, after we've covered all 4 examples, I will give you the last tip: since Laravel 8.43, the framework has a built-in N+1 Query detector!

In addition to the Laravel Debugbar for inspection, you can add a code to the prevention of this problem.

You need to add two lines of code to app/Providers/AppServiceProvider.php:

use Illuminate\Database\Eloquent\Model;
 
class AppServiceProvider extends ServiceProvider
{
    public function boot()
    {
        Model::preventLazyLoading(! app()->isProduction());
    }
}

Now, if you launch any page that contains an N+1 Query problem, you will see an error page, something like this:

Optimize Laravel Performance

This will show you the exact "dangerous" code that you may want to fix and optimize.

Note that this code should be executed only on your local machine or testing/staging servers, live users on production servers should not see this message, cause that would be a security issue. That's why you need to add a condition like ! app()->isProduction(), which means that your APP_ENV value in the .env file is not "production".

Interestingly, this prevention didn't work for me when trying with the last example of Media Library. Not sure if it's because it comes from the external package, or because of polymorphic relations. So, my ultimate advice still stands: use Laravel Debugbar to monitor the number of queries and optimize accordingly.

You can find all 4 examples in the free repository on Github and play around with them.

Wish you have a great speed performance in your projects!

Posted on May 15, 2023
Click for Source

Optimize Laravel Performance: 4 Examples of N+1 Query Problems