Facing a problem in query time

I am a beginner in mongodb. I am working on two collections bookings and booking_logs. I have around 7k bookings and 14k booking_logs.

I want to get booking with their logs for which booking_logs exist with bookings. I have used the following query.

db.bookings.aggregate([
{$lookup:{
    "from":"booking__logs",
    "localField":"_id",
    "foreignField":"booking_id",
    "as":"booking_time_logs"
    }},
{"$match":{"booking_time_logs":{"$not":{"$size":0}}}}
])

But, the query is taking too much time to execute. Is there any way to reduce the time? Please answer. Thank you!

Hi @Sahildeep_Kaur, welcome to the community.
Do you have an index on the booking_id field in the booking__logs collection?
If not, please create an index using the following command:

db.booking__logs.createIndex({"booking_id": 1}) 

Hopefully, this will reduce the query time significantly.

But, if this doesn’t help, and you have any doubts, please feel free to reach out to us.

Thanks and Regards.
Sourabh Bagrecha,
Curriculum Services Engineer

2 Likes

Thank you, @SourabhBagrecha. I have already used booking_id index. It helped but still the response time of query is 0.678s. I want to reduce it. Can you please suggest something else, It would be very helpful.

Hi @Sahildeep_Kaur, thanks for confirming that.
Can you please share a sample document from both collections? Also, please mention the version of MongoDB you are using.

In case you have any doubts, please feel free to reach out to us.

Thanks and Regards.
Sourabh Bagrecha,
Curriculum Services Engineer

Thank you, Please accept invite on atlas to view the sample database.

Hi @Sahildeep_Kaur, I am reaching out to you via DMs.

Thanks and Regards.
Sourabh Bagrecha,
Curriculum Services Engineer