Eloquent Performance: 4 Examples of N+1 Query Problems

Published on by

Eloquent Performance: 4 Examples of N+1 Query Problems image

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.

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.

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.

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:

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 }}

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:

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...

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.

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:

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!

PovilasKorop photo

Creator of Courses and Tutorials at Laravel Daily

Cube

Laravel Newsletter

Join 40k+ other developers and never miss out on new tips, tutorials, and more.

image
Paragraph

Manage your Laravel app as if it was a CMS – edit any text on any page or in any email without touching Blade or language files.

Visit Paragraph
Laravel Forge logo

Laravel Forge

Easily create and manage your servers and deploy your Laravel applications in seconds.

Laravel Forge
Tinkerwell logo

Tinkerwell

The must-have code runner for Laravel developers. Tinker with AI, autocompletion and instant feedback on local and production environments.

Tinkerwell
No Compromises logo

No Compromises

Joel and Aaron, the two seasoned devs from the No Compromises podcast, are now available to hire for your Laravel project. ⬧ Flat rate of $7500/mo. ⬧ No lengthy sales process. ⬧ No contracts. ⬧ 100% money back guarantee.

No Compromises
Kirschbaum logo

Kirschbaum

Providing innovation and stability to ensure your web application succeeds.

Kirschbaum
Shift logo

Shift

Running an old Laravel version? Instant, automated Laravel upgrades and code modernization to keep your applications fresh.

Shift
Bacancy logo

Bacancy

Supercharge your project with a seasoned Laravel developer with 4-6 years of experience for just $2500/month. Get 160 hours of dedicated expertise & a risk-free 15-day trial. Schedule a call now!

Bacancy
LoadForge logo

LoadForge

Easy, affordable load testing and stress tests for websites, APIs and databases.

LoadForge
Paragraph logo

Paragraph

Manage your Laravel app as if it was a CMS – edit any text on any page or in any email without touching Blade or language files.

Paragraph
Lucky Media logo

Lucky Media

Bespoke software solutions built for your business. We ♥ Laravel

Lucky Media
Lunar: Laravel E-Commerce logo

Lunar: Laravel E-Commerce

E-Commerce for Laravel. An open-source package that brings the power of modern headless e-commerce functionality to Laravel.

Lunar: Laravel E-Commerce
DocuWriter.ai logo

DocuWriter.ai

Save hours of manually writing Code Documentation, Comments & DocBlocks, Test suites and Refactoring.

DocuWriter.ai
LaraJobs logo

LaraJobs

The official Laravel job board

LaraJobs
All Green logo

All Green

All Green is a SaaS test runner that can execute your whole Laravel test suite in mere seconds so that you don't get blocked – you get feedback almost instantly and you can deploy to production very quickly.

All Green
Larafast: Laravel SaaS Starter Kit logo

Larafast: Laravel SaaS Starter Kit

Larafast is a Laravel SaaS Starter Kit with ready-to-go features for Payments, Auth, Admin, Blog, SEO, and beautiful themes. Available with VILT and TALL stacks.

Larafast: Laravel SaaS Starter Kit
SaaSykit: Laravel SaaS Starter Kit logo

SaaSykit: Laravel SaaS Starter Kit

SaaSykit is a Laravel SaaS Starter Kit that comes with all features required to run a modern SaaS. Payments, Beautiful Checkout, Admin Panel, User dashboard, Auth, Ready Components, Stats, Blog, Docs and more.

SaaSykit: Laravel SaaS Starter Kit
Rector logo

Rector

Your partner for seamless Laravel upgrades, cutting costs, and accelerating innovation for successful companies

Rector

The latest

View all →
Automatic Blade Formatting on Save in PhpStorm image

Automatic Blade Formatting on Save in PhpStorm

Read article
PhpStorm 2024.1 Is Released With a Integrated Terminal, Local AI Code Completion, and More image

PhpStorm 2024.1 Is Released With a Integrated Terminal, Local AI Code Completion, and More

Read article
Laravel Prompts Adds a Multi-line Textarea Input, Laravel 11.3 Released image

Laravel Prompts Adds a Multi-line Textarea Input, Laravel 11.3 Released

Read article
Bartender Is an Opinionated Way to Authenticate Users Using Laravel Socialite image

Bartender Is an Opinionated Way to Authenticate Users Using Laravel Socialite

Read article
Jeffrey Way's PhpStorm Setup in 2024 image

Jeffrey Way's PhpStorm Setup in 2024

Read article
Easily Optimize PDFs in Laravel with the Optimizer Package image

Easily Optimize PDFs in Laravel with the Optimizer Package

Read article