0

I have question about mongoDB (version:"3.4.10") index arrays as I see they don't work correctly. Maybe I am doing something wrong.

I have schedule document that has some props that aren't important for this question. But each schedule has it's plan (occurrence) period in array.

{ .... "plans": [ { "startDateTime": "2018-01-04T00:00:00Z", "endDateTime": "2018-01-04T23:59:59Z" }, { "startDateTime": "2018-01-11T00:00:00Z", "endDateTime": "2018-01-11T23:59:59Z" } ... ] },

Now I need to search schedule documents by that array item and find all schedules that fit in that period. I have created index plans.startDateTime and plans.endDateTime.

When I perform following query using compas option explain plan I get bad really bad results.

{"Plans.StartDateTime": {$lt: new Date ('2018-01-10')}, "Plans.EndDateTime": {$gte: new Date ('2018-01-15')} }

Results are (this is in test environment where number of documents are really low, in production ratio would be even higher)

  • Documents Returned:2823
  • Index Keys Examined:65708
  • Documents Examined:11554

When I go little bit deeper in analysis I got following (meaning that mongo is ignoring plan end date in index search):

"indexBounds": { "Plans.StartDateTime": [ "(true, new Date(1515542400000))" ], "Plans.EndDateTime": [ "[MinKey, MaxKey]" ] },

Can somebody please tell me how to create better indexes for following search because this one isn't working?

Car90
  • 65
  • 1
  • 1
  • 4

1 Answers1

1

In order to find all scheduleDocuments having at least one plan overlapping with a given time interval (e.g. 2018-01-10 and 2018-01-14) you have to use $elemMatch MongoDB operator.

db.scheduleDocuments.find({
    plans: {
        $elemMatch: {
            startDateTime: { $lte: ISODate("2018-01-14Z") },
            endDateTime: { $gt: ISODate("2018-01-10Z") }
        }
    }
});

The rule used to test for overlapping intervals can be found here.

This search performs a collection scan, unless you create an index on the array.

db.scheduleDocuments.createIndex({
    "plans.startDateTime": 1,
    "plans.endDateTime": 1
});

Thanks to the index, unmatching documents in the collection are not scanned at all. An IXSCAN is performed and only matching documents are accessed to be fetched and returned.

Marcello
  • 879
  • 6
  • 20