What's the difference between $hint and hint?

Working with Data
, ,
1

Hello All,
I was running an aggregate, on which I am using the hint operator of Mongodb. But when I ran a query with $hint it took nearly 7.2 seconds, and when I ran the same query with hint, it took nearly 3 seconds.

I am unable to find the reason why it is happening. Can you please let me know what can be the reason or am I executing the query in the wrong manner?

Query:

db.collection_name.aggregate([
  {
    "$match": {
      "field_name": {
        //Some Conditions
      },
      "field_name2": {
        // list of object ids to be filtered out
    }
  },
  {
    "$sort": {
      "name": 1
    }
  },
  {
    "$facet": {
      "new_createdField": [
        {
          "$skip": 0
        },
        {
          "$limit": 10
        }
      ],
      "totalCount": [
        {
          "$count": "count"
        }
      ]
    }
  },
  {
    "$project": {
      "new_createdField": 1,
      "totalCount": {
        "$arrayElemAt": [
          "$totalCount.count",
          0
        ]
      }
    }
  }
],{
$hint: ({
field_name: 1,
field_name2: 1,
name: 1
})
);

The same query is executed with the hint-operator.
Additional Information
field_name, field_name2, and name are already in Compound Index.

3

Hi @Mehul_Sanghvi,

The $hint operator has been deprecated since MongoDB 3.2 and was likely only ever supported for find operations.

When performing an aggregate, as can be seen in the syntax documentation a hint field can be used to pass an index hint to the operation.

I am unable to find the reason why it is happening. Can you please let me know what can be the reason or am I executing the query in the wrong manner?

Replace $hint with hint (no leading $ sign) and you should get the desired result.

2 Likes
4

Hi @alexbevi
Okay. One more thing is that in the documentation of the hint operator, it is stated :

The $hint operator forces the query optimizer to use a specific index to fulfill the query. Specify the index either by the index name or by document.
Use $hint for testing query performance and indexing strategies. The mongo shell provides a helper method hint() for the $hint operator.

Does this mean that if I don’t have any index and use a hint operator in my database, it would forcefully create an index on that field just for experimental or testing purposes?

5

No, using $hint with an index specification that doesn’t exist will not create the index

1 Like
6

Okay. Thank You @alexbevi

1 Like
7

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