0

I have a MongoDB query

Schema: Demo
{
  a: String,
  b: Number,
  c: Bool,
  d: Number
}

Query:
db.Demo.find({ a:'test', c: true }).sort({b:-1, d: -1}).limit(40).explain("executionStats")

I have tried applying these index:

TYPE 1 INDEX

  1. db.Demo.createIndex({b-1, d:-1})
  2. db.Demo.createIndex({a:1})
  3. db.Demo.createIndex({c:1})

TYPE 2 INDEX

  1. db.Demo.createIndex({a:1, c:1, b:-1, d:-1})

MongoDB always ignore TYPE 2 index as rejected plans and use TYPE 1 Index. But TYPE One is taking more time and I think it can be more optimized.

Explain Results by TYPE 1 QUERY.

....
"executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 20,
        "executionTimeMillis" : 351,
        "totalKeysExamined" : 647,
        "totalDocsExamined" : 647,
}
Robins Gupta
  • 3,143
  • 3
  • 34
  • 57

1 Answers1

0

Found the Perfect Index for the Query:

This has been explained in this blog

Optimizing MongoDB Compound Indexes

Query will be db.Demo.createIndex({c:1, b:-1, d:-1, a:1})

Robins Gupta
  • 3,143
  • 3
  • 34
  • 57
  • because c is bool type and its cardinality is pretty low, I assume you don't need to include c in your compound indexes. – kakashi Jun 06 '18 at 23:18