CODEWITHSUNDEEP
mongodbaggregation-framework
sangita
sangita

Reputation: 191

Optimizing mongo query for better response

I am trying to optimize mongodb query for better response

db.myReports.find({
"CheckInDate": {
    "$gte" : ISODate("2015-01-12T00:00:00Z"),
    "$lte" : ISODate("2015-03-31T00:00:00Z")
},
"SubscriberPropertyId":  NumberLong(47984),
"ChannelId": {
    "$in": [701, 8275]
},
"PropertyId": {
    "$in": [47984, 3159, 5148, 61436, 66251, 70108]
},
"LengthOfStay": 1
},     {
    "CheckInDate": 1,
   "SubscriberPropertyId": 1,
    "ChannelId": 1,
    "PropertyId": 1
});

Currently it is taking around 3 minutes just to find data from 3 million records.

One Document from collection

{
  "_id" : ObjectId("54dba46c320caf5a08473074"),
    "OptimisationId" : NumberLong(1),
    "ScheduleLogId" : NumberLong(3),
      "ReportId" : NumberLong(4113235),
   "SubscriberPropertyId" : NumberLong(10038),
   "PropertyId" : NumberLong(18166),
   "ChannelId" : 701,
  "CheckInDate" : ISODate("2014-09-30T18:30:00Z"),
  "LengthOfStay" : 1,
 "OccupancyIndex" : 1.0,
 "CreatedDate" : ISODate("2014-09-11T06:31:08Z"),
  "ModifiedDate" : ISODate("2014-09-11T06:31:08Z"),

 }

INDEX created is: 

db.myReports.getIndexes();
[
        {
            "v" : 1,
            "key" : {
                    "_id" : 1
            },
            "name" : "_id_",
            "ns" : "db.myReports"
    },
    {
            "v" : 1,
            "key" : {
                    "CheckInDate" : 1,
                    "SubscriberPropertyId" : 1,
                    "ReportId" : 1,
                    "ChannelId" : 1,
                    "PropertyId" : 1
            },
            "name" :      
 "CheckInDate_1_SubscriberPropertyId_1_ReportId_1_Channe

 lId_1_PropertyId_1",
            "ns" : "db.myReports"
    },
    {
            "v" : 1,
            "key" : {
                    "CheckInDate" : 1
            },
            "name" : "CheckInDate_1",
            "ns" : "db.myReports"
    }
]

I have created index on possible entities

Upvotes: 1

Views: 93

Answers (1)

mnemosyn
mnemosyn

Reputation: 46331

Put equality queries first, then range queries:

db.myReports.find({
  "SubscriberPropertyId":  NumberLong(47984),
  "ChannelId": {
    "$in": [701, 8275]
  },
  "PropertyId": {
    "$in": [47984, 3159, 5148, 61436, 66251, 70108]
  },
  "CheckInDate": {
    "$gte" : ISODate("2015-01-12T00:00:00Z"),
    "$lte" : ISODate("2015-03-31T00:00:00Z")
  },
  "LengthOfStay": 1 // low selectivity, move to the end
}, {
  "CheckInDate": 1,
  "SubscriberPropertyId": 1,
  "ChannelId": 1,
  "PropertyId": 1
});

Make sure the index fits, i.e make the index SubscriberPropertyId, ChannelId, PropertyId, CheckInDate. LengthOfStay probably has too low selectivity to make sense in an index, depends on your data.

That should reduce the nscanned significantly, but getting 300k results will take its time (actually reading them, I mean)

Upvotes: 1

Related Questions