Eager loading, n+1 issue and query optimization in laravel

In Laravel there are few ways to optimize queries. One way is eager loading. First let's see what is eager loading.

When you handle relationships between columns, The default way is Lazy loading. When you access a related model in Laravel, a separate query is made to the database to fetch that related data. This is known as lazy loading. Let us see with and example.

Assume you have two models: Post and Comment, where a Post has many Comments.

$posts = Post::all();

foreach ($posts as $post) {
    foreach ($post->comments as $comment) {
        // Each iteration fetches comments in a separate query
    }
}

Let us now see how this works in query level. Imagine you have N number of posts in your table. Above code will run N+1 queries. Let me explain how it happens,

First Query: Fetch All Posts

SELECT * FROM `posts`;

N Queries: Fetch Comments for Each Post
For each Post object in $posts, Laravel will perform a separate query to fetch the related comments. If there are "N" posts, it will run "N" queries, one for each post.

The query structure looks like this:

SELECT * FROM `comments` WHERE `comments`.`post_id` = 1 AND `comments`.`post_id` IS NOT NULL;
SELECT * FROM `comments` WHERE `comments`.`post_id` = 2 AND `comments`.`post_id` IS NOT NULL;
...
SELECT * FROM `comments` WHERE `comments`.`post_id` = N AND `comments`.`post_id` IS NOT NULL;

Summary of Queries:

1 Query to fetch all posts from the posts table.
N Queries to fetch comments for each post, where N is the number of posts retrieved in the first query.

Now you may see there are N+1 queries. This is called N+1 issue. For prevent this we use Eager Loading

Eager Loading

$posts = Post::with('comments')->get();

foreach ($posts as $post) {
    foreach ($post->comments as $comment) {
        // Comments are already loaded
    }
}

Here we use "with" eloquent method for eager loading. Let's see how this optimize things. Instead of N+1 queries it runs only two queries now.

Query to Fetch All Posts:

SELECT * FROM `posts`;

Query to Fetch All Related Comments:

SELECT * FROM `comments` WHERE `post_id` IN (1, 2, 3, ... , N);

Instead of N+1 queries in earlier now we have only two queries. And this increases the performance a lot.

After Eager Loading

In some cases also Eager Loading may be problematic. Let's see an example. Imagine Post model (posts table) have multiple relationships.

$posts = Post::with(['user', 'comments'])->get();

foreach ($posts as $post) {

}

This will result in three queries. Fetch posts, users related to posts and comments related to posts. Imagine you have millions of users and comments in your database. Then it will be better to take all these records using a single query.

In a scenario like this taking everything inside one query is faster than taking three queries and mapping. The reason is databases has optimized to face such scenarios. In theoretically speaking this is the reason

Single Query: Joins allow you to fetch all the related data in a single query, reducing the overhead of multiple database round trips.
Database-Level Optimization: Joins leverage the database's ability to optimize query execution, often resulting in faster data retrieval, especially with proper indexing.
Reduced Memory Usage: Since joins combine data into a single result set, they can be more memory-efficient, especially when you don't need to instantiate many related models in your application.

Instead of using with, you could use a join to fetch the data more efficiently:

$posts = Post::select('posts.*', 'users.name as author_name', 'comments.content as comment_content')
    ->join('users', 'posts.user_id', '=', 'users.id')
    ->join('comments', 'posts.id', '=', 'comments.post_id')
    ->get();

But if you check this query carefully you can see "users.name as author_name" and "comments.content as comment_content". This is because two tables having same column called 'name'. But for eager loading situations like this are automatically handled.

Bottom Line:

Joins: If you're dealing with multiple relationships and large datasets, using joins can be more efficient. This is because joins consolidate the data retrieval into a single, optimized query, minimizing the overhead of multiple queries and reducing memory usage.
Eager Loading: If you're not dealing with massive datasets or the relationships aren't too complex, eager loading is a safer and more convenient option. It automatically handles potential conflicts (like duplicate column names) and allows you to work with related models in a clean and organized way without needing to worry about query complexity.

When to Choose Which:

Joins: Ideal for scenarios where performance is critical, and you're dealing with large volumes of data. Use joins when you need to minimize the number of queries and optimize database-level operations.
Eager Loading: Best for smaller or moderate datasets where ease of use and code readability are more important than raw performance. It simplifies working with related data and avoids the manual handling required with joins.

Conclusion:

For large, complex relationships: Joins are typically better for performance.
For smaller, simpler scenarios: Eager Loading offers convenience and safety.

You could use tools like Laravel Telescope to see the queries and times for them.