Eloquent Performance Tips

Unoptimized Eloquent queries make your project slower and expensive to run.

As a Laravel developer, you should always measure your Eloquent queries and make them fast and efficient.

Unfortunately, many Laravel developers use Eloquent without paying attention to some critical parts and they end up writing slow and inefficient queries.

This is the first in a series of Eloquent optimization posts where I show you practical solutions to common performance problems.

In this post, I will introduce you to the Laravel debugger and explain the N+1 problem.

#Database Diagram

During this post, I will be referring to the following diagram whenever I mention a model or a table:

You do not need to create all these tables, since the tips can be applied in any kind of project.

#What is N+1 issue?

The N+1 issue is a common problem in ORM systems such as Eloquent.

Say you want to iterate over a bunch of users and list all the posts for each individual one:

John
    Post 1
    Post 2
    Post 3
    ...
Ahmad
    Post 1
    Post 2
    ...
Dania
    ...

Laravel implementation:

// Controllers\UsersController

public function posts(User $user)
{
    return view('user.posts', ['users' => User::paginate()]);
}

<!-- users/posts.blade.php -->

@foreach ($users as $user)
    <h3>{{ $user->name }}</h3>
    <ul>
        @foreach ($user->posts as $post)
            <li>{{ $post->title }}</li>
        @endforeach
    </ul>
@endforeach

The above code introduced a N+1 problem, which means that we issued one SELECT statement:

SELECT * FROM users;

And N (total number of posts) additional SELECT statements:

SELECT * FROM posts where user_id = 1;
SELECT * FROM posts where user_id = 2;
SELECT * FROM posts where user_id = 3;
# ...

In the upcoming posts I will show you techniques to avoid the N+1 problem and make your queries fast and efficient.

Your focus should be on reducing the time that the SQL queries take to run. That is the metric that matters.

#Laravel Debugger

Laravel Debugger is a great tool for measuring your database performance and debugging other application layers.

If you are already familiar with Laravel Debugger, skip this part.

In case you have not installed it yet:

composer require barryvdh/laravel-debugbar --dev

Head to your application and you will see a debug bar.

Since we are dealing with the database, pay attention to the Queries and Models tabs:

• Queries: Executed queries on the current route.
• Models: Loaded models on the current route.

You should also pay attention to the execution time:

• Queries Execution Time: How long did it take to execute all the queries?
• Page Load Time: How long did it take to load the entire page?

Run the following query within a controller:

// App\Http\Controllers\UsersController

User::take(10)->get();

If you inspect the Queries tab you will see one single query:

select * from `users` limit 10

And if you inspect the Models tab, you will see ten models -- this means that ten Eloquent models were loaded on the current route.

In the upcoming post I will show you how to optimize your Eloquent queries by only selecting the needed columns.

#Summary

• N+1 problem -- a common ORM issue where one initial query triggers N additional queries, one for each related record. This is the single most frequent cause of slow Eloquent performance.
• Laravel Debugger -- an essential tool for measuring query count, execution time, and loaded models on any given route.
• Focus on query time -- the metric that matters is how long your SQL queries take to run. Measure first, optimize second.