0

I have a multikey index on an array field for a collection. When I query the collection with an $elemMatch for the field the query is very slow despite of the index. So I did and explain and the index bounds seems to be incorrect.

I have mongoDB version 3.2.11

Here is the collection document structure:

{
"_id" : ObjectId("5c3b2def2157ed8004f6df42"),
...
"optins" : [ 
    {
        "active" : true,
        "campaign" : "campaign-partenaires",
        "register_date" : ISODate("2014-07-29T08:39:14.000Z")
    }, 
    {
        "active" : false,
        "campaign" : "campaign-top-20",
        "register_date" : ISODate("2014-07-29T08:39:14.000Z"),
        "unregister_date" : ISODate("2018-03-01T09:37:58.000Z"),
    }, 
    ...
 ]
}

The index definition:

createIndex(
   {
      'optins.campaign':1,
      'optins.active':1,
      'optins.register_date':1,
      'optins.unregister_date':1
    },
    {
      background:true,
      sparse:false
    }
)

The query:

db.getCollection('lead').find(
{
    optins :    {
        $elemMatch : {
            campaign: "campaign-partenaires",
            active : true,
            register_date : {
                $gt: ISODate("2014-07-29T08:39:14.000Z"),
                $lt: ISODate("2019-07-29T08:39:14.000Z")
            }
        }
    }
})

The explain winning plan input stage:

{
    "stage" : "IXSCAN",
    "keyPattern" : {
        "optins.campaign" : 1.0,
        "optins.active" : 1.0,
        "optins.register_date" : 1.0,
        "optins.unregister_date" : 1.0
    },
    "indexName" : "optins.campaign_1_optins.active_1_optins.register_date_1_optins.unregister_date_1",
    "isMultiKey" : true,
    "isUnique" : false,
    "isSparse" : false,
    "isPartial" : false,
    "indexVersion" : 1,
    "direction" : "forward",
    "indexBounds" : {
        "optins.campaign" : [ 
            "[\"campaign-partenaires\", \"campaign-partenaires\"]"
        ],
        "optins.active" : [ 
            "[true, true]"
        ],
        "optins.register_date" : [ 
            "(true, new Date(1564389554000))"
        ],
        "optins.unregister_date" : [ 
            "[MinKey, MaxKey]"
        ]
    }
}

So I don't understand why the bounds on the unregister_date is

(true, new Date(1564389554000)) where Date(1564389554000) is ISODate("2019-07-29T08:39:14.000Z")

But I think it should be

[ISODate("2014-07-29T08:39:14.000Z",ISODate("2019-07-29T08:39:14.000Z")]

Any help please?

Javaddict
  • 483
  • 1
  • 4
  • 15
  • Which version? It does `"optins.register_date" : ["(new Date(1406623154000), new Date(1564389554000))"],` in v4.0.6 – Alex Blex Apr 15 '19 at 11:23
  • The version is 3.2.11 – Javaddict Apr 15 '19 at 12:13
  • There were some problems with range queries over multikey indices before 3.3.8 https://jira.mongodb.org/browse/SERVER-15086 Check related bug reports for workarounds. v3.2 was decommissioned half year ago, you really should consider an upgrade. – Alex Blex Apr 15 '19 at 13:22

0 Answers0