Bidirection traversal of the index in MongoDB

I have two fields named Length and Width in my MongoDB collection. To sort both fields in ascending order, I created a compound index on both fields in ascending order.

db.productDimensions.createIndex({ Length: 1, Width: 1});

However, if I need to sort one field in ascending order and the other in descending order, does MongoDB’s compound index support bidirectional traversal? If not, what would be an optimal solution for efficiently handling various sorting requirements, especially considering potential future expansions where additional fields might need to be sorted? Would creating compound indexes for all possible combinations be the best approach?

What I’ve attempted is searching through MongoDB’s official documentation, but I didn’t find any relevant information regarding Bidirection traversal of the index in MongoDB.

2 Likes

Hi @SAMRAT_N_A,

Let’s understand the word “bidirectional traversal” in MongoDB index:

  1. When you create an index:

    db.productDimensions.createIndex({ Length: 1, Width: 1 });
    

    The efficient index that you can use is bidirectional, either both ascending, or descending:

    // efficient
    db.productDimensions.find().sort({ Length: 1, Width: 1 });
    db.productDimensions.find().sort({ Length: -1, Width: -1 });
    

    This will not use an index for just sorting but if you add the first property as a filter then this will use an index but will take additional work to reverse the order:

    // not efficient
    db.productDimensions.find().sort({ Length: -1, Width: 1 });
    db.productDimensions.find().sort({ Length: 1, Width: -1 });
    
  2. When you create an index:

    db.productDimensions.createIndex({ Length: 1, Width: -1 });
    

    The efficient index that you can use is bidirectional, either both ascending, descending, or descending, ascending:

    // efficient
    db.productDimensions.find().sort({ Length: 1, Width: -1 });
    db.productDimensions.find().sort({ Length: -1, Width: 1 });
    

    This will not use an index for just sorting but if you add the first property as a filter then this will use an index but will take additional work to reverse the order:

    // not efficient
    db.productDimensions.find().sort({ Length: 1, Width: 1 });
    db.productDimensions.find().sort({ Length: -1, Width: -1 });
    

However, you can use an explain plan to check different combination as per your use case and check the exact performance in detail:

2 Likes

@turivishal Thank you, this will be beneficial. I’ll verify the behavior you mentioned above using the ‘explain()’ method.

1 Like

Hello @turivishal. Could you assist me with another matter related to the previous one? I’ve established a search index on my collection and employed the subsequent query:

db.productDimensions.explain().aggregate([{
    $search: {
      "text": {
        "path": "name",
        "query": "productNameXYZ"
      }
    }
  },
  {$sort: { name: 1 }}
]);

Upon executing this query with explain(), I noticed the absence of a winningPlan field. How can I ascertain whether the search index is being utilized?

Hi @SAMRAT_N_A,

You can ask your question in a new post, there are many experienced MongoDB masters in this community so everyone could get a chance to contribute and share their knowledge according to their availability and you can get quick answers.

I don’t know more about the $search index, but make sure that you have created the search index in your atlas cluster, as I see in your query, you missed the index name in the $search stage.

For more, you can follow the documentation,

2 Likes

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.