CODEWITHSUNDEEP
mongodbindexing
Satoko
Satoko

Reputation: 133

MongoDB index is not being used

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?

Upvotes: 9

Views: 2472

Answers (1)

anhlc
anhlc

Reputation: 14469

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

Upvotes: 2

Related Questions