Sort of MongoDB using index

Question

Below is the status of the index status of the collection that I want to let you see.

> db.histories.getIndexes();
[
  {
    "v" : 1,
    "key" : {
      "_id" : 1
    },
    "name" : "_id_",
    "ns" : "development.histories"
  },
  {
    "v" : 1,
    "key" : {
      "hoge_id" : 1,
      "created_at" : 1
    },
    "name" : "hoge_id_1_created_at_1",
    "ns" : "development.histories",
    "background" : true
  },
  {
    "v" : 1,
    "key" : {
      "created_at" : 1
    },
    "name" : "created_at_1",
    "ns" : "development.histories",
    "background" : true
  }
]

And, I executed the following query.

> db.histories.find({hoge_id: ObjectId("5a5c171010ebfb1a2c901008")}).sort( { created_at: -1 } ).limit(1).explain("executionStats");

And, the result was below.

{
  "queryPlanner" : {
    "plannerVersion" : 1,
    "namespace" : "development.histories",
    "indexFilterSet" : false,
    "parsedQuery" : {
      "hoge_id" : {
        "$eq" : ObjectId("5a5c171010ebfb1a2c901008")
      }
    },
    "winningPlan" : {
      "stage" : "LIMIT",
      "limitAmount" : 1,
      "inputStage" : {
        "stage" : "FETCH",
        "inputStage" : {
          "stage" : "IXSCAN",
          "keyPattern" : {
            "hoge_id" : 1,
            "created_at" : 1
          },
          "indexName" : "hoge_id_1_created_at_1",
          "isMultiKey" : false,
          "isUnique" : false,
          "isSparse" : false,
          "isPartial" : false,
          "indexVersion" : 1,
          "direction" : "backward",
          "indexBounds" : {
            "hoge_id" : [
              "[ObjectId('5a5c171010ebfb1a2c901008'), ObjectId('5a5c171010ebfb1a2c901008')]"
            ],
            "created_at" : [
              "[MaxKey, MinKey]"
            ]
          }
        }
      }
    },
    "rejectedPlans" : [ ]
  },
  "executionStats" : {
    "executionSuccess" : true,
    "nReturned" : 1,
    "executionTimeMillis" : 0,
    "totalKeysExamined" : 1,
    "totalDocsExamined" : 1,
    "executionStages" : {
      "stage" : "LIMIT",
      "nReturned" : 1,
      "executionTimeMillisEstimate" : 0,
      "works" : 2,
      "advanced" : 1,
      "needTime" : 0,
      "needYield" : 0,
      "saveState" : 0,
      "restoreState" : 0,
      "isEOF" : 1,
      "invalidates" : 0,
      "limitAmount" : 1,
      "inputStage" : {
        "stage" : "FETCH",
        "nReturned" : 1,
        "executionTimeMillisEstimate" : 0,
        "works" : 1,
        "advanced" : 1,
        "needTime" : 0,
        "needYield" : 0,
        "saveState" : 0,
        "restoreState" : 0,
        "isEOF" : 0,
        "invalidates" : 0,
        "docsExamined" : 1,
        "alreadyHasObj" : 0,
        "inputStage" : {
          "stage" : "IXSCAN",
          "nReturned" : 1,
          "executionTimeMillisEstimate" : 0,
          "works" : 1,
          "advanced" : 1,
          "needTime" : 0,
          "needYield" : 0,
          "saveState" : 0,
          "restoreState" : 0,
          "isEOF" : 0,
          "invalidates" : 0,
          "keyPattern" : {
            "hoge_id" : 1,
            "created_at" : 1
          },
          "indexName" : "hoge_id_1_created_at_1",
          "isMultiKey" : false,
          "isUnique" : false,
          "isSparse" : false,
          "isPartial" : false,
          "indexVersion" : 1,
          "direction" : "backward",
          "indexBounds" : {
            "hoge_id" : [
              "[ObjectId('5a5c171010ebfb1a2c901008'), ObjectId('5a5c171010ebfb1a2c901008')]"
            ],
            "created_at" : [
              "[MaxKey, MinKey]"
            ]
          },
          "keysExamined" : 1,
          "dupsTested" : 0,
          "dupsDropped" : 0,
          "seenInvalidated" : 0
        }
      }
    }
  },
  "serverInfo" : {
    "host" : "b9cb1b8d1fc1",
    "port" : 27017,
    "version" : "3.2.18",
    "gitVersion" : "4c1bae566c0c00f996a2feb16febf84936ecaf6f"
  },
  "ok" : 1
}

The result is fast, I guess it's due to creating index on created_at.

ref. "totalDocsExamined" : 1, "executionTimeMillis" : 0

Then, I did exection the following query. The difference of previous is the field used for sort.

> db.histories.find({hoge_id: ObjectId("5a5c171010ebfb1a2c901008")}).sort( { id: -1 } ).limit(1).explain("executionStats");

And, the result was below.

> db.histories.find({hoge_id: ObjectId("5a5c171010ebfb1a2c901008")}).sort( { id: -1 } ).limit(1).explain("executionStats");
{
  "queryPlanner" : {
    "plannerVersion" : 1,
    "namespace" : "development.histories",
    "indexFilterSet" : false,
    "parsedQuery" : {
      "hoge_id" : {
        "$eq" : ObjectId("5a5c171010ebfb1a2c901008")
      }
    },
    "winningPlan" : {
      "stage" : "SORT",
      "sortPattern" : {
        "id" : -1
      },
      "limitAmount" : 1,
      "inputStage" : {
        "stage" : "SORT_KEY_GENERATOR",
        "inputStage" : {
          "stage" : "FETCH",
          "inputStage" : {
            "stage" : "IXSCAN",
            "keyPattern" : {
              "hoge_id" : 1,
              "created_at" : 1
            },
            "indexName" : "hoge_id_1_created_at_1",
            "isMultiKey" : false,
            "isUnique" : false,
            "isSparse" : false,
            "isPartial" : false,
            "indexVersion" : 1,
            "direction" : "forward",
            "indexBounds" : {
              "hoge_id" : [
                "[ObjectId('5a5c171010ebfb1a2c901008'), ObjectId('5a5c171010ebfb1a2c901008')]"
              ],
              "created_at" : [
                "[MinKey, MaxKey]"
              ]
            }
          }
        }
      }
    },
    "rejectedPlans" : [ ]
  },
  "executionStats" : {
    "executionSuccess" : true,
    "nReturned" : 1,
    "executionTimeMillis" : 1215,
    "totalKeysExamined" : 1034353,
    "totalDocsExamined" : 1034353,
    "executionStages" : {
      "stage" : "SORT",
      "nReturned" : 1,
      "executionTimeMillisEstimate" : 1120,
      "works" : 1034357,
      "advanced" : 1,
      "needTime" : 1034355,
      "needYield" : 0,
      "saveState" : 8080,
      "restoreState" : 8080,
      "isEOF" : 1,
      "invalidates" : 0,
      "sortPattern" : {
        "id" : -1
      },
      "memUsage" : 297,
      "memLimit" : 33554432,
      "limitAmount" : 1,
      "inputStage" : {
        "stage" : "SORT_KEY_GENERATOR",
        "nReturned" : 0,
        "executionTimeMillisEstimate" : 950,
        "works" : 1034355,
        "advanced" : 0,
        "needTime" : 1,
        "needYield" : 0,
        "saveState" : 8080,
        "restoreState" : 8080,
        "isEOF" : 1,
        "invalidates" : 0,
        "inputStage" : {
          "stage" : "FETCH",
          "nReturned" : 1034353,
          "executionTimeMillisEstimate" : 650,
          "works" : 1034354,
          "advanced" : 1034353,
          "needTime" : 0,
          "needYield" : 0,
          "saveState" : 8080,
          "restoreState" : 8080,
          "isEOF" : 1,
          "invalidates" : 0,
          "docsExamined" : 1034353,
          "alreadyHasObj" : 0,
          "inputStage" : {
            "stage" : "IXSCAN",
            "nReturned" : 1034353,
            "executionTimeMillisEstimate" : 330,
            "works" : 1034354,
            "advanced" : 1034353,
            "needTime" : 0,
            "needYield" : 0,
            "saveState" : 8080,
            "restoreState" : 8080,
            "isEOF" : 1,
            "invalidates" : 0,
            "keyPattern" : {
              "hoge_id" : 1,
              "created_at" : 1
            },
            "indexName" : "hoge_id_1_created_at_1",
            "isMultiKey" : false,
            "isUnique" : false,
            "isSparse" : false,
            "isPartial" : false,
            "indexVersion" : 1,
            "direction" : "forward",
            "indexBounds" : {
              "hoge_id" : [
                "[ObjectId('5a5c171010ebfb1a2c901008'), ObjectId('5a5c171010ebfb1a2c901008')]"
              ],
              "created_at" : [
                "[MinKey, MaxKey]"
              ]
            },
            "keysExamined" : 1034353,
            "dupsTested" : 0,
            "dupsDropped" : 0,
            "seenInvalidated" : 0
          }
        }
      }
    }
  },
  "serverInfo" : {
    "host" : "b9cb1b8d1fc1",
    "port" : 27017,
    "version" : "3.2.18",
    "gitVersion" : "4c1bae566c0c00f996a2feb16febf84936ecaf6f"
  },
  "ok" : 1
}
>

The result is late this time.

ref. "totalDocsExamined" : 1034353, "executionTimeMillis" : 1215

About totalDocsExamined, That's all in all documents.

Regardress that id is enable for index as created_at, but, when it is sorted using id, the result is late?

VeryLazyBoy · Accepted Answer · 2018-02-15 04:31:41Z

1

For your 1st query:

db.histories.find({hoge_id: ObjectId("5a5c171010ebfb1a2c901008")}).sort( { created_at: -1 } ).limit(1).explain("executionStats");

MongoDB is optimizing the performance by using the compound index on hoge_id and created_at. It firstly looks at the hoge_id and then it uses the index of created_at to sort the query results. In this way, the sort operation can be very fast because of efficient usage of compound index.

However, for your 2nd query:

db.histories.find({hoge_id: ObjectId("5a5c171010ebfb1a2c901008")}).sort( { id: -1 } ).limit(1).explain("executionStats");

Since there is no compound index on hoge_id and id(you only have a single index on id), MongoDB is actually manually sorting results by id.

More info on sorting with compound index can be found here.

answered Feb 15, 2018 at 4:31

VeryLazyBoy

4304 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yujiro Over a year ago

I understood. Thanks!

Collectives™ on Stack Overflow

Sort of MongoDB using index

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related