How to create a MongoDB index with isodate? 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(&quot;Coll&quot;).find({&quot;Field1.date&quot; : {
                    $gte: ISODate(&quot;2019-11-27T00:00:00.000Z&quot;),
                    $lte: ISODate(&quot;2019-11-28T00:00:00.000Z&quot;)                  
                }},{
    _id: 1,
    &quot;Field2&quot;: 1,
    &quot;Field3&quot;:1,
     Field4: 1
})

and i created an index like this:
//    db.Coll.createIndex(
//    {
//           _id: 1,
//    &quot;Field2&quot;: 1,
//     Field4: 1,
//    &quot;Field1.date&quot;:1
//    },
//    {background:true , name: &quot;idx_name_date&quot;})
//    

but it seems this &quot;field1.date&quot; doesnt work with ISODate.
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, &quot;Field1.date&quot;: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 { &quot;Field1.date&quot;: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.