Finding N+1 Queries in Laravel

Posted by on 15th Jul 2018

Finding N+1 Queries in Laravel

Introduction

Using Laravel's Eloquent Active-Record, it becomes incredibly easy to define relations between your models. But with all this ease of use, developers might not notice that their application could be suffering in terms of performance, because of underlying database calls.

To give you a better understanding of what I mean, let's take a simple example. Let's take this simple ER Diagram (built using our ER Diagram package) as an example.

Our application is a blogging platform, so we have posts, which have an author and every author has a profile. That's pretty simple.

The N+1 Problem

Alright, with our application in place, we want to display a table that contains all our posts along with each post's author name.

One way of doing it would be:

// This is in your controller
$posts = Post::all();

return view('posts')->with('posts', $posts);
<table>
    @foreach($posts as $post)
        <tr>
            <td>{{ $post->title }}</td>
            <td>{{ $post->author->name }}</td>
        </tr>
    @endforeach
</table>

And this would work. We get our table with the post title and the author name. But there's a problem with this approach. In the background, we will create and execute a database query every time we want to access the author name for a post.

Eager Loading

To fix this issue, we can go and eager load the author relation, when retrieving our posts. This would only result in two queries.

One to get all the posts and a second one to get all authors of these posts.

Eager-Loading in Laravel is really easy. All you need to do is, tell your model to load a specific relation:

// This is in your controller
$posts = Post::with('author')->get();

return view('posts')->with('posts', $posts);

When you develop your application and you do not pay attention to this, you might run into this issue. To figure out if you do, there was no really good way of doing this - you would need to take a look at your database queries and figure out, if these queries come from a model relation that is not eager loaded etc.

Introducing the Laravel N+1 Query Detector

Our latest package is going to help you with this. It's called "Laravel N+1 Query Detector" and it does exactly this.

You can install it via composer:

composer require beyondcode/laravel-query-detector --dev

And after you've installed it - and your application is in debug mode - all you need to do is browse your application. If the N+1 Query Detector finds a model relation that was not eager loaded and is called more than once, it will show you an alert dialog and tells you how to fix it.

The package also allows you to exclude certain relations from the checks, has a customizable threshold level and you can also write the found queries into your log file instead of showing an alert.

Take a look at the package documentation to find out more about it.

I hope that this package will help you improve your Laravel application performance.