Database examples

Performance speaks for itself: 4 examples of N+1 query problems

The eloquent performance is usually the main reason for slow Laravel projects. Much of this is a so-called “N+1 query problem”. In this article, I’ll show a few different examples of what to look out for, including cases where the problem is “hidden” in unexpected places in the code.

What is the N+1 query problem

In short, it’s when the Laravel code performs too many database queries. This happens because Eloquent allows developers to write readable syntax with templates, without delving into the “magic” that happens under the hood.

This isn’t just a problem with Eloquent, or even Laravel: it’s well known in the development industry. Why is it called “N+1”? Because, in Eloquent’s case, it queries ONE row of the database and then performs another query for EACH related record. Thus, N queries plus the record itself add up to N+1.

To solve it, we need to query related records beforehand, and Eloquent allows us to do this easily, with what is called eager loading. But before we get to the solutions, let’s discuss the problems. I will show you 4 different cases.


Case 1. “Normal” N+1 request.

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 is happening here? the $book->author part will perform an additional DB query for each book, to get its author.

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

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

And yes, you understood correctly: if you have 100 books on the list, you will have 101 queries to the database. Terrible performance, even though the code looked “innocent”, didn’t it.

The fix is ​​to load the relation in advance, immediately in the controller, with the fast loading I mentioned earlier:

// Instead of:

$books = Book::all();

// You should do:

$books = Book::with('author')->get();

The result is much better – only 2 queries:

Sample N+1 Query 1 - Fast Load

When using eager loading, Eloquent fetches all records in the table and performs ONE query against the associated database table, passing those IDs from that table. And then every time you call $book->authorit loads the result of the variable which is already in memory, no need to query the database again.

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

Always use the debug bar. And sow false data.

This bottom bar is a Laravel Debugbar package. To use it, you just need to install it:

composer require barryvdh/laravel-debugbar --dev

And that’s it, it will show bottom bar on all pages. You just need to enable debugging with the .env variable APP_DEBUG=truewhich is a default for locales.

Safety Notice: make sure that when your project goes live, you will have APP_DEBUG=false on that server, otherwise regular users of your website will see the debug bar 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 won’t show obvious problems until you have more data on the pages. So using Debugbar is only part of the advice.

Additionally, I also recommend having seed classes that would generate false data. Preferably lots of data, so you can see how your project performs “in real life” if you imagine it growing successfully in the months or years to come.

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

class BookSeeder extends Seeder

{

public function run()

{

Book::factory(10000)->create();

}

}

Then browse the website and see what Debugbar shows you.

There are also other alternatives to Laravel Debugbar:


Case 2. Two important symbols.

Let’s say 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.

The 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)

{{ $author->name }}

{{ $author->books()->count() }}

@endforeach

Sounds legit, right? And it works. But look at the debug bar data below.

N+1 Query Example 2 - Bad performance

But wait, you’d say we’re using eager loading, Author::with('books')so why are there so many requests?

Because, in Blade, $author->books()->count() does not actually load this relation from memory.

  • $author->books() denotes the METHOD of relationship
  • $author->books means that the DATA are loaded into memory

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

Sample N+1 Query 2 - Good Performance

So be careful what exactly you are using – the relation method or the data.

Note that in this particular example, there is an even better solution. If you only need the computed aggregate data of the relationship, without the full models, you should only load the aggregates, like withCount :

// Controller:

$authors = Author::withCount('books')->get();

// Blade:

{{ $author->books_count }}

Sample N+1 Query 2 - Better Performance

As a result, there will only be ONE query in the database, not even two queries. Also, the memory will not be “polluted” with relationship data, so RAM will also be saved.


Case 3. “Hidden” relation in the accessor.

Let’s take a similar example: a list of authors, with the column if the author is active: “Yes” or “No”. This activity is defined by whether the author has at least one book, and it is computed as an accessor inside the Author model.

The controller code could be:

public function index()

{

$authors = Author::all();

return view('authors.index', compact('authors'));

}

Blade file:

@foreach($authors as $author)

{{ $author->name }}

{{ $author->is_active ? 'Yes' : 'No' }}

@endforeach

This “is_active” is defined in the Eloquent model:

use IlluminateDatabaseEloquentCastsAttribute;

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 “old” method definition syntax getIsActiveAttribute()it will also work in the latest version of Laravel.

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

N+1 Query Example 3 - Poor performance

Yes, we can solve it by eagerly loading the books into the controller. But in this case, my general advice is avoid using relations in accessors. Because an accessor is usually used when displaying data, and in the future, someone else may use that accessor in another Blade file, and you won’t control what that controller looks like.

In other words, Accessor is supposed to be a reusable method of formatting the data, so you don’t control when and how it will be reused. In your current case, you can avoid the N+1 query, but in the future, someone else might not think of 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 may encounter unexpected N+1 requests if you are 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 point this out as a flaw in the package, but rather as an example of the importance of debugging what’s going on under the hood.

The 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 SpatieMediaLibraryHasMedia;

use SpatieMediaLibraryInteractsWithMedia;

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)

{{ $book->title }}

@endforeach

That getFirstMediaUrl() The method is taken from the official package documentation.

Now if we load the page and look at the debug bar…

N+1 Query Example 4 - Bad performance

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

So the package is doing a poor job of performance? Well, no, because the official documentation explains how to retrieve media files for a specific model object, for a book, but not for the list. This part of the list you need to figure out for yourself.

If we dig a little deeper, in the trait InteractsWithMedia of the package, we find this relationship which is self-included in all the models:

public function media(): MorphMany

{

return $this->morphMany(config('media-library.media_model'), 'model');

}

So if we want all the media files to be 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.

Sample N+1 Query 4 - Good Performance

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


The integrated solution against N+1 requests

Now, after covering the 4 examples, I’ll give you the last tip: since Laravel 8.43, the framework has a built-in N+1 request detector!

In addition to the Laravel debug bar for inspection, you can add code to prevent this issue.

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

use IlluminateDatabaseEloquentModel;

class AppServiceProvider extends ServiceProvider

{

public function boot()

{

Model::preventLazyLoading(! app()->isProduction());

}

}

Now, if you launch a page with an N+1 query problem, you’ll see an error page, something like this:

N+1 request - Prevent lazy loading

This will show you the exact “dangerous” code that you might want to fix and optimize.

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

Interestingly, this prevention didn’t work for me when I tried with the latest Media Library example. I don’t know if it’s because it comes from the external package or because of polymorphic relationships. So my ultimate advice still stands: use Laravel Debugbar to monitor the number of requests and optimize accordingly.

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

I wish you great speed performance in your projects!