I've create and index for a query and its running is less then 1 second to retrieve millions of rows, but as soon as I add greater than ISODATE, it doesnt use indexes anymore.

the query is this one:

db.getCollection("Coll").find({"Field1.date" : {
                    $gte: ISODate("2019-11-27T00:00:00.000Z"),
                    $lte: ISODate("2019-11-28T00:00:00.000Z")                  
    _id: 1,
    "Field2": 1,
     Field4: 1

and i created an index like this:

//    db.Coll.createIndex(
//    {
//           _id: 1,
//    "Field2": 1,
//     Field4: 1,
//    "Field1.date":1
//    },
//    {background:true , name: "idx_name_date"})

but it seems this "field1.date" doesnt work with ISODate.

  • How does Field1.date look like? – Wernfried Domscheit Aug 8 '20 at 19:06
  • Why an index on _id? This is the primary key and indexed by default. – Wernfried Domscheit Aug 8 '20 at 19:07

The order of the fields in the index definition is important.

The indexed values are sorted in the order the fields appear in the spec, so in the index: { _id: 1, Field2: 1,Field4: 1, "Field1.date":1}, the values will be sorted first by _id, then by Field2, etc.

This means that in order to find documents with dates in the range you specified, it would have to examine every index entry.

It that case is probably just as efficient to scan all of the documents.

Instead build an index on { "Field1.date":1, _id: 1, Field2: 1,Field4: 1}. That will allow the query executor to find all of the documents by scanning a single subset of the index, and it will have all of the projected fields available to avoid fetching any documents.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.