MongoDB index is not being used

Question

I have a collection of questions with index on modified.

{
  "v" : 1,
  "key" : {
      "modified" : 1
  },
  "name" : "modified_1",
  "ns" : "app23568387.questions",
  "background" : true,
  "safe" : null
}

But when I query the questions with modified field, mongo does not use this index.

db.questions.find({modified: ISODate("2016-07-20T20:58:20.662Z")}).explain(true);

It returns

{
  "cursor" : "BasicCursor",
  "isMultiKey" : false,
  "n" : 0,
  "nscannedObjects" : 19315626,
  "nscanned" : 19315626,
  "nscannedObjectsAllPlans" : 19315626,
  "nscannedAllPlans" : 19315626,
  "scanAndOrder" : false,
  "indexOnly" : false,
  "nYields" : 384889,
  "nChunkSkips" : 0,
  "millis" : 43334,
  "allPlans" : [ 
      {
          "cursor" : "BasicCursor",
          "isMultiKey" : false,
          "n" : 0,
          "nscannedObjects" : 19315626,
          "nscanned" : 19315626,
          "scanAndOrder" : false,
          "indexOnly" : false,
          "nChunkSkips" : 0
      }
  ],
  "server" : "c387.candidate.37:10387",
  "filterSet" : false,
  "stats" : {
      "type" : "COLLSCAN",
      "works" : 19624020,
      "yields" : 384889,
      "unyields" : 384889,
      "invalidates" : 3,
      "advanced" : 0,
      "needTime" : 19315627,
      "needFetch" : 0,
      "isEOF" : 1,
      "docsTested" : 19315626,
      "children" : []
  }
}

When I use hint(), mongo throws an error bad hint. I have another collection of folders which has exactly the same index and the query uses the index. (returns "cursor" : "BtreeCursor modified_1" for explain())

What could be the difference between questions and folders? Is it possible that the index is "broken" even though getIndexes() returns it? If so, what can I do to fix it?

Could you run that query with .explain("allPlansExecution") and then update your quesiton with the entire explain plan output. The essential parts are the chosen plan and the rejected plan sections since these will show us why MongoDB did not choose the modified_1 index. Also, this: "When I use hint(), mongo throws an error bad hint" suggests that there is no index with the name you supplied to the hint() method. — glytching
– glytching, Commented Jan 14, 2018 at 12:27
@glytching I updated the question with the result. (Since our db version is 2.6, I had to pass true.) modified_1 is not in allPlans. Does this mean, the index does not exist even though it returns in getIndexes? — Satoko
– Satoko, Commented Jan 18, 2018 at 22:26
@Satoko MongoDB 2.6 has been deprecated for more than 2 years now. Consider upgrading to version 3.2 at least, and see if the problem persist — felix
– felix, Commented Jan 19, 2018 at 7:07

anhlc · Accepted Answer · 2018-01-18 23:29:23Z

2

+50

It seems your index is not completely built in background. You can check this by using db.currentOp() command:

https://docs.mongodb.com/v3.0/reference/method/db.currentOp/#currentop-index-creation

Also check the mongod.log to see any error on the index building process.

The simple way to fix is to drop the index and create it again

answered Jan 18, 2018 at 23:29

anhlc

14.6k4 gold badges38 silver badges42 bronze badges

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

2 Comments

glytching Over a year ago

That's a possibility. The inconsistency between (a) the bad hint response and (2) the statement that this index is returned by getIndexes() strongly suggests an issue with the index creation process. A related suggestion: if the OP is querying a replicaset with eventual consistency and the query is hitting a secondary then the issue might be that the index has not built successfully on the secondary.

Satoko Over a year ago

@glytching Yes. I had been only using secondary to run the query. I tried explain() on primary, it returned "cursor" : "BtreeCursor modified_1". Later primary and secondary were switched and that caused index to be built again(?). Now both have and use index for the query. Thank you.

Collectives™ on Stack Overflow

MongoDB index is not being used

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related