Before diving into the article lets have a glance at
What are n+1 queries?
n+1 queries are a common performance issue in applications that interact with a database, particularly in the context of Object-Relational Mapping (ORM) frameworks like Laravel's Eloquent. The problem occurs when an application makes one initial query to retrieve a set of data (the "1" query), followed by a separate query for each item in that set (the "n" queries).
This leads to n+1 queries, which can significantly slow down the application, especially when dealing with large datasets.
$posts = Post::all(); // 1 query
foreach ($posts as $post) {
$comments = $post->comments; // n queries
}
// leading into the problem of n+1 queriesHow do you Detect n+1 Queries?
It's very easy to overlook the n+1 issue with a small dataset, and most of the time we don't even realize that we have done this. To avoid this the best way is to use beyondcode's Query detector
Installation
composer require beyondcode/laravel-query-detector --devThe package will automatically register itself and will start detecting n+1 queries.
whenever there is a n+1 queries issues, you will get a alert link this.
Before getting into the "fix it" part, let's have a brief view of the impact this issue has on performance.
Impact of n+1 issue on Performance
- Increased Load on the Database
- Slower Response Times
- Resource Consumption
- Slower Page Loads
- Inconsistent Performance
- Reduced Scalability
How to Fix the n+1 queries issue?
There are several ways, the n+1 issue can be resolved. Some of them are mentioned below.
- Eager Loading
- Join Query
- Batch Queries
- Caching
- Database Optimization
Eager Loading
Continuing the example above the flowing are some of the ways to implement Eager Loading.
Using `with()`
// Eager load comments with posts in one query
$posts = Post::with("comments")->get();
foreach ($posts as $post) {
// Now, no additional query is made here
$comments = $post->comments;
}Using `load()`
$posts = Post::all();
$posts->load("comments");
...When to use `with()` vs `load()`
If you need the data with the model, then go for `with()`.
If you need the data on an optional basis, then `load()` is the way to go.
Pro Tip
One additional thing you can do is, use the query builder with ` select()` and use condition in relations.
$posts = Post::select("id, author_id")->with("comments", function ($q) {
$q->where("is_active", true);
})->get();Join Query
Occasionally, it's a smart idea to fetch all the data in one query using the join statements. It can be done with raw queries.
// Raw Query Example (using PDO):
$sql = "SELECT * FROM authors JOIN books ON authors.id = books.author_id";
$stmt = $pdo->query($sql);
$authorsAndBooks = $stmt->fetchAll(PDO::FETCH_ASSOC);
// with laravel eloquent
$authorsAndBooks = DB::table('authors')
->join('books', 'authors.id', '=', 'books.author_id')
->get();Batch Queries
When you have a big dataset to deal with, it's a good idea to use chunks for processing data.
Author::chunk(100, function ($authors) {
foreach ($authors as $author) {
// Process each author...
}
});Caching
Another effective way is caching.
// the data will be stored for "$minutes" in cache and will served from there.
$users = Cache::remember('posts', $minutes, function () {
return Post::with("comments")->get();
});Database Optimization
Database indexing boosts query performance by speeding up data retrieval, reducing the need to scan entire tables. It improves search efficiency, optimizes storage access patterns, supports faster joins, and enhances overall database performance.
Feel free to suggest other ways possible, if I missed some in comment section.
Update
As suggested by Punyapal Shah on pinkary, we don't need an additional package to achieve the n+1 check. It can be achieved by adding the following to your AppServiceProvider.
/**
* Configure the models.
*/
private function configureModels(): void
{
Model::shouldBeStrict(! $this->app->isProduction());
}
