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 queries

How 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 --dev

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