Why MongoDb sort is slow with lookup collections

Question

I have two collections in my mongodb database as follows:

employee_details with approximately 330000 documents which has department_id as a reference from departments collection

departments collections with 2 fields _id and dept_name

I want to join the above two collections using department_id as foreign key by using lookup method. Join works fine but the mongo query execution takes long time when I add sort.

Note: The execution is fast If I remove the sort object or If I remove the lookup method.

I have referred several posts in different blogs and SO, but none of them give a solution with sort.

My query is given below:

db.getCollection("employee_details").aggregate([
  {
    $lookup: {
      from: "departments",
      localField: "department_id",
      foreignField: "_id",
      as: "Department"
    }
  },
  { $unwind: { path: "$Department", preserveNullAndEmptyArrays: true } },
  { $sort: { employee_fname: -1 } },
  { $limit: 10 }
]);
 

Can someone give a method to make the above query to work without delay, as my client cannot compromise with the performance delay. I hope there is some method to fix the performance issue as nosql is intented to handle large database.

Any indexing methods is available there? so that I can use it along with my same collection structure.

Thanks in advance.

Answer 1

Currently lookup will be made for every employee_details which means for 330000 times, but if we first sort and limit before lookup, it will be only 10 times. This will greatly decrease query time.

db.getCollection('employee_details').aggregate([
    {$sort      : {employee_fname: -1}},
    {$limit     :10},
    {
        $lookup : {
            from         : "departments",
            localField   : "department_id",
            foreignField : "_id",
            as           : "Department"
        }
    },
    { $unwind   : { path: "$Department", preserveNullAndEmptyArrays: true }},
]) 

After trying this, if you even want to decrease the response time you can define an index on the sort field.

db.employee_details.createIndex( { employee_fname: -1 } )