Create Index on array

[Tom Vo]

Dear all

I have collection Devices with data sample below.

db.Devices.find()

{

        "_id" : NumberLong(1),

        "CapturedDate" : [

                [

                        1,

                        ISODate("2012-05-10T02:07:50.159Z")

                ],

                [

                        2,

                        ISODate("2012-05-09T12:31:50.299Z")

                ]

        ],

        "OSName" : "Windows",

        "DeviceType" : "Mobile"

}

{

        "_id" : NumberLong(2),

        "CapturedDate" : [

                [

                        1,

                        ISODate("2012-04-10T06:59:50.123Z")

                ],

                [

                        3,

                        ISODate("2012-12-09T12:06:50.243Z")

                ]

        ],

        "OSName" : "Iphone",

        "DeviceType" : "Mobile"

}

I want to find size of "CapturedDate" great than 2, and this query run slowly. I try to create index on "CapturedDate" but performance not improve. Please give me to your suggestion to solute this trouble.

db.Devices.ensureIndex( { CapturedDate : 1 } )

db.Devices.find({"CapturedDate":{$size:2}},{CapturedDate:1}).explain()

{

        "cursor" : "BasicCursor",

        "nscanned" : 1218300,

        "nscannedObjects" : 1218300,

        "n" : 73,

        "millis" : 21544,

        "nYields" : 0,

        "nChunkSkips" : 0,

        "isMultiKey" : false,

        "indexOnly" : false,

        "indexBounds" : {

        }

}

>

> db.Devices.find( { $where: "this.CapturedDate.length > 1" } ).hint({CapturedDate : 1}).explain()

{

        "cursor" : "BtreeCursor CapturedDate_1",

        "nscanned" : 1218376,

        "nscannedObjects" : 1218376,

        "n" : 73,

        "millis" : 23276,

        "nYields" : 0,

        "nChunkSkips" : 0,

        "isMultiKey" : true,

        "indexOnly" : false,

        "indexBounds" : {

                "CapturedDate" : [

                        [

                                {

                                        "$minElement" : 1

                                },

                                {

                                        "$maxElement" : 1

                                }

                        ]

                ]

        }

Thanks and Regards

Tom Vo

[Thomas Rueckstiess]

Hi Tom,

according to the documentation, "Queries cannot use indexes for the $size portion of a query".

http://docs.mongodb.org/manual/reference/operator/size/

In order to make a query on the number of documents in an array efficient, you could add a separate field 'numCapturedDates' to your schema and manually increase and decrease it when you add/remove elements to/from the array. An index on 'numCapturedDates' would then help you efficiently retrieve documents with a certain number of elements.

Regards,

Thomas

[Tom Vo]

Dear Thomas

I'll consider as your suggestion.Thank you for your kindly.

--
You received this message because you are subscribed to the Google
Groups "mongodb-user" group.
To post to this group, send email to mongod...@googlegroups.com
To unsubscribe from this group, send email to
mongodb-user...@googlegroups.com
See also the IRC channel -- freenode.net#mongodb

--

Thanks and Best Regards

Tom Vo