What
When we make N+1 Query to the Database instead of 1 single optimized query.
Examples
1 Getting ALL User Blog Posts
// Getting all the users
const users = await User.find();
// Looping through all the users and getting the posts
for(const user of users){
user.posts = await Post.find({userId: user.id}))
}
2 Fetching Subscription for Multiple Orgs
const orgs = await Org.find();
for(const org of orgs){
orgs.subscription = await Subscription.find({orgId: org.id});
}
As you can see in the above examples, we are making N+1 queries to the database.
1 - Query to get all the users
N - Queries to get all the posts for each user
Solution
We can optimize above query by using Aggregation and Lookup to get all the posts for each user in single query.
// For MongoDB
const users = await this.userModel.aggregate([
{
$match:{
_id: userId
}
},{
$lookup: {
from: "posts",
localField: "_id",
foreignField: "userId",
as: "posts"
}
}
])
Same way we can do this in SQL using Joins
// Left Outer Join - Keeps all the rows from the left table even if there is no matched data in the right table, NULL is shown from Right Table
// Inner Join - Only matched rows are shown
// FULL Join - ALL rows from both table.
SELECT * FROM orgs LEFT JOIN subscriptions ON orgs.id = subscription.orgId;
Top comments (0)