Eloquent Performance: 3 Most Common Mistakes

The performance of our applications is one of the top things we should care about. Inefficient Eloquent or DB queries are probably no.1 reason for bad performance. In this tutorial, I will show you the top 3 mistakes developers make when it comes to Eloquent performance, and how to fix them.

Mistake 1: Too Many DB Queries

One of the biggest and most repeating mistakes is the N+1 query issue. This is generally caused by a lot of queries to the database and not using eager loading.

Example 1: Eager Load Relationship

One of the most common example looks like this:

app/Http/Controllers/PostController.php

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

And imagine you're using the Spatie Media Library package to load media files.

In the Blade file, you would use user and media relationships directly without preloading them:

resources/views/posts/index.blade.php

<ul>
    @foreach($posts as $post)
        <li>
            {{ $post->title }} / By {{ $post->user->name }}
            @foreach($post->getMedia() as $media)
                {{ $media->getUrl() }}
            @endforeach
        </li>
    @endforeach
</ul>

This produces a result similar to this, which contains a lot of database calls to get related users and media for a post:

To fix this, we can simply modify the controller to eager load the relationship like so:

1. Change all() to get()
2. Add the with(['user', 'media']) method to load the relationships

app/Http/Controllers/PostController.php

public function index()
{
    $posts = Post::with(['user', 'media'])->get();
 
    return view('posts.index', compact('posts'));
}

As a result, you will see only 3 queries being executed to load all the required data for the view:

Example 2: Counting Related Models

Another common mistake can be demonstrated by displaying how many posts each user has. See the example:

app/Http/Controllers/UserController.php

public function index()
{
    $users = User::with(['posts'])->get();
 
    return view('users.index', compact('users'));
}

And for the view, we can have one of the following: resources/views/users/index.blade.php

@foreach($users as $user)
    <li>{{ $user->name }} / Posts {{ $user->posts()->count() }}</li>
@endforeach

or

@foreach($users as $user)
    <li>{{ $user->name }} / Posts {{ $user->posts->count() }}</li>
@endforeach

Which one is correct? posts()->count() or posts->count()?

There's a big difference between them. Let's take for example

<li>{{ $user->name }} / Posts {{ $user->posts()->count() }}</li>

This will take the user and then attempt to load posts directly from the database due to us having posts() as it creates a new SQL query to get the count of posts for each user:

Instead, we should aim to have $user->posts as this will return our already preloaded posts:

<li>{{ $user->name }} / Posts {{ $user->posts->count() }}</li>

And this will use our already loaded data to reduce the number of queries we have:

Mistake 2: Loading Too Much Data

Another common performance mistake is loading too much data while all you need is a small set of it.

Example 1: with() VS withCount()

The first example will include a counting mistake which usually loads all the data to get a number out of it.

app/Http/Controllers/UserController.php

public function index()
{
    $users = User::with(['posts'])->get();
 
    return view('users.index', compact('users'));
}

resources/views/users/index.blade.php

<ul>
    @foreach($users as $user)
        <li>{{ $user->name }} / Posts {{ $user->posts->count() }}</li>
    @endforeach
</ul>

As you can see, we take all posts and then just count them in our view. This works, but produces more queries that it needs.

Let us update our code:

app/Http/Controllers/UserController.php

public function index()
{
    $users = User::withCount(['posts'])->get();
 
    return view('users.index', compact('users'));
}

resources/views/users/index.blade.php

<ul>
    @foreach($users as $user)
        <li>{{ $user->name }} / Posts {{ $user->posts_count }}</li>
    @endforeach
</ul>

By using withCount(['posts']) we are telling Eloquent to count the posts directly within the database. This produces only 1 query that is much more efficient than the previous ones:

Keep in mind that this will only retrieve counted results and is meant to optimize the counting of them. You will not be able to access any post information this way.

Example 2: Loading Too Many Columns

Another example is loading only the required columns and not everything for a Model. This is great when you have big database tables, and you just need one or two columns.

In this case, we want to display the title of the post and the user's name, so we filter that in the Controller:

app/Http/Controllers/PostController.php

public function index()
{
    $posts = Post::with(
        [
            'user' => function ($query) {
                $query->select('id', 'name');
            },
            'media'
        ]
    )->get(['author_id', 'title']);
 
    return view('posts.index', compact('posts'));
}

And with our view file looks like this:

resources/views/posts/index.blade.php

<ul>
    @foreach($posts as $post)
        <li>
            {{ $post->title }} / By {{ $post->user->name }}
            @foreach($post->getMedia() as $media)
                {{ $media->getUrl() }}
            @endforeach
        </li>
    @endforeach
</ul>

We can see that we are only taking specific columns with our SQL query:

This way we are not loading any unnecessary data for posts and users.

Mistake 3: Load Data First, Filter Later

This was noticed a lot in forums like laracasts where people tend to first do the database operation (like get()) and then do the filtering on the collection.

This is not the best way to do it as it will load all the data and then filter it out. To fix this, we can simply load the correct data from the database directly:

An example of a bad case. Focus on the get()->where(...) part, which first executes the database query and then applies filters:

app/Http/Controllers/PostController.php

public function index()
{
    $posts = Post::with(['user', 'media'])
        ->get()
        ->where('created_at', '>=', now()->subDays(7));
 
    return view('posts.index', compact('posts'));
}

This results in us getting all the data from the database and then filtering it with PHP which is slower:

Imagine if we have 200,000 posts in our database! We don't need to load all posts even from 5 years ago, when all we need is 7 days of posts.

To get around this, we can change the order of the logic:

app/Http/Controllers/PostsController.php

public function index()
{
    $posts = Post::with(['user', 'media'])
        ->where('created_at', '>=', now()->subDays(7))
        ->get();
 
    return view('posts.index', compact('posts'));
}

And this will produce a correct database query to get only 7 days worth of posts:

Final thoughts

As these are the most common patterns I've seen in my career, I hope this will help you to improve your code and make it more efficient.

To learn more about Eloquent and its performance you can check my courses like Eloquent: The Expert Level or Better Eloquent Performance.