MongoDB with OR and Range Indexes

Posted by LMH on Stack Overflow See other posts from Stack Overflow or by LMH
Published on 2013-06-24T20:36:29Z Indexed on 2013/06/24 22:22 UTC
Read the original article Hit count: 201

Filed under:
|

I have a query:

{"$query"=>{"user_id"=>"512f7960534dcda22b000491", "$or"=>[{"when_tz"=>{"$gte"=>2010-06-24 04:00:00 UTC, "$lt"=>2010-06-25 04:00:00 UTC}}, {"when_tz"=>{"$gte"=>2011-06-24 04:00:00 UTC, "$lt"=>2011-06-25 04:00:00 UTC}}, {"when_tz"=>{"$gte"=>2012-06-24 04:00:00 UTC, "$lt"=>2012-06-25 04:00:00 UTC}}], "_type"=>{"$in"=>["FacebookImageItem", "FoursquareImageItem", "InstagramItem", "TwitterImageItem", "Image"]}}, "$explain"=>true, "$orderby"=>{"when_tz"=>1}}

And an index:

{ user_id: 1, _type: 1, when_tz: 1 }

Explain:

{"cursor"=>"BtreeCursor user_id_1__type_1_facebook_id_1 multi", "isMultiKey"=>false, "n"=>28, "nscannedObjects"=>15094, "nscanned"=>15098, "nscannedObjectsAllPlans"=>181246, "nscannedAllPlans"=>241553, "scanAndOrder"=>true, "indexOnly"=>false, "nYields"=>12, "nChunkSkips"=>0, "millis"=>2869, "indexBounds"=>{"user_id"=>[["512f7960534dcda22b000491", "512f7960534dcda22b000491"]], "_type"=>[["FacebookImageItem", "FacebookImageItem"], ["FoursquareImageItem", "FoursquareImageItem"], ["Image", "Image"], ["InstagramItem", "InstagramItem"], ["TwitterImageItem", "TwitterImageItem"]], "facebook_id"=>[[{"$minElement"=>1}, {"$maxElement"=>1}]]}, "allPlans"=>[{"cursor"=>"BtreeCursor user_id_1__type_1_facebook_id_1 multi", "n"=>28, "nscannedObjects"=>15094, "nscanned"=>15098, "indexBounds"=>{"user_id"=>[["512f7960534dcda22b000491", "512f7960534dcda22b000491"]], "_type"=>[["FacebookImageItem", "FacebookImageItem"], ["FoursquareImageItem", "FoursquareImageItem"], ["Image", "Image"], ["InstagramItem", "InstagramItem"], ["TwitterImageItem", "TwitterImageItem"]], "facebook_id"=>[[{"$minElement"=>1}, {"$maxElement"=>1}]]}}, {"cursor"=>"BtreeCursor user_id_1__type_1_twitter_id_1 multi", "n"=>28, "nscannedObjects"=>15094, "nscanned"=>15097, "indexBounds"=>{"user_id"=>[["512f7960534dcda22b000491", "512f7960534dcda22b000491"]], "_type"=>[["FacebookImageItem", "FacebookImageItem"], ["FoursquareImageItem", "FoursquareImageItem"], ["Image", "Image"], ["InstagramItem", "InstagramItem"], ["TwitterImageItem", "TwitterImageItem"]], "twitter_id"=>[[{"$minElement"=>1}, {"$maxElement"=>1}]]}}, {"cursor"=>"BtreeCursor user_id_1__type_1_instagram_id_1 multi", "n"=>28, "nscannedObjects"=>15094, "nscanned"=>15097, "indexBounds"=>{"user_id"=>[["512f7960534dcda22b000491", "512f7960534dcda22b000491"]], "_type"=>[["FacebookImageItem", "FacebookImageItem"], ["FoursquareImageItem", "FoursquareImageItem"], ["Image", "Image"], ["InstagramItem", "InstagramItem"], ["TwitterImageItem", "TwitterImageItem"]], "instagram_id"=>[[{"$minElement"=>1}, {"$maxElement"=>1}]]}}, {"cursor"=>"BtreeCursor user_id_1__type_1_foursquare_id_1 multi", "n"=>28, "nscannedObjects"=>15094, "nscanned"=>15097, "indexBounds"=>{"user_id"=>[["512f7960534dcda22b000491", "512f7960534dcda22b000491"]], "_type"=>[["FacebookImageItem", "FacebookImageItem"], ["FoursquareImageItem", "FoursquareImageItem"], ["Image", "Image"], ["InstagramItem", "InstagramItem"], ["TwitterImageItem", "TwitterImageItem"]], "foursquare_id"=>[[{"$minElement"=>1}, {"$maxElement"=>1}]]}}, {"cursor"=>"BtreeCursor user_id_1_phash_1", "n"=>21, "nscannedObjects"=>15097, "nscanned"=>15097, "indexBounds"=>{"user_id"=>[["512f7960534dcda22b000491", "512f7960534dcda22b000491"]], "phash"=>[[{"$minElement"=>1}, {"$maxElement"=>1}]]}}, {"cursor"=>"BtreeCursor user_id_1_aperature_1_shutter_speed_1_when_tz_1", "n"=>25, "nscannedObjects"=>35, "nscanned"=>15097, "indexBounds"=>{"user_id"=>[["512f7960534dcda22b000491", "512f7960534dcda22b000491"]], "aperature"=>[[{"$minElement"=>1}, {"$maxElement"=>1}]], "shutter_speed"=>[[{"$minElement"=>1}, {"$maxElement"=>1}]], "when_tz"=>[[{"$minElement"=>1}, {"$maxElement"=>1}]]}}, {"cursor"=>"BtreeCursor user_id_1_image_hash_1", "n"=>22, "nscannedObjects"=>15097, "nscanned"=>15097, "indexBounds"=>{"user_id"=>[["512f7960534dcda22b000491", "512f7960534dcda22b000491"]], "image_hash"=>[[{"$minElement"=>1}, {"$maxElement"=>1}]]}}, {"cursor"=>"BtreeCursor user_id_1_time_zone_guessed_1_when_tz_-1", "n"=>23, "nscannedObjects"=>32, "nscanned"=>15097, "indexBounds"=>{"user_id"=>[["512f7960534dcda22b000491", "512f7960534dcda22b000491"]], "time_zone_guessed"=>[[{"$minElement"=>1}, {"$maxElement"=>1}]], "when_tz"=>[[{"$maxElement"=>1}, {"$minElement"=>1}]]}}, {"cursor"=>"BtreeCursor user_id_1_time_zone_guessed_1_when_tz_1", "n"=>24, "nscannedObjects"=>33, "nscanned"=>15097, "indexBounds"=>{"user_id"=>[["512f7960534dcda22b000491", "512f7960534dcda22b000491"]], "time_zone_guessed"=>[[{"$minElement"=>1}, {"$maxElement"=>1}]], "when_tz"=>[[{"$minElement"=>1}, {"$maxElement"=>1}]]}}, {"cursor"=>"BtreeCursor user_id_1_time_zone_guessed_1_when_utc_-1", "n"=>23, "nscannedObjects"=>15097, "nscanned"=>15097, "indexBounds"=>{"user_id"=>[["512f7960534dcda22b000491", "512f7960534dcda22b000491"]], "time_zone_guessed"=>[[{"$minElement"=>1}, {"$maxElement"=>1}]], "when_utc"=>[[{"$maxElement"=>1}, {"$minElement"=>1}]]}}, {"cursor"=>"BtreeCursor user_id_1_time_zone_guessed_1_when_utc_1", "n"=>24, "nscannedObjects"=>15097, "nscanned"=>15097, "indexBounds"=>{"user_id"=>[["512f7960534dcda22b000491", "512f7960534dcda22b000491"]], "time_zone_guessed"=>[[{"$minElement"=>1}, {"$maxElement"=>1}]], "when_utc"=>[[{"$minElement"=>1}, {"$maxElement"=>1}]]}}, {"cursor"=>"BtreeCursor user_id_1_original_shared_item_id_1", "n"=>24, "nscannedObjects"=>15097, "nscanned"=>15097, "indexBounds"=>{"user_id"=>[["512f7960534dcda22b000491", "512f7960534dcda22b000491"]], "original_shared_item_id"=>[[{"$minElement"=>1}, {"$maxElement"=>1}]]}}, {"cursor"=>"BtreeCursor user_id_1__type_1_s3_tmp_file_1 multi", "n"=>28, "nscannedObjects"=>15094, "nscanned"=>15097, "indexBounds"=>{"user_id"=>[["512f7960534dcda22b000491", "512f7960534dcda22b000491"]], "_type"=>[["FacebookImageItem", "FacebookImageItem"], ["FoursquareImageItem", "FoursquareImageItem"], ["Image", "Image"], ["InstagramItem", "InstagramItem"], ["TwitterImageItem", "TwitterImageItem"]], "s3_tmp_file"=>[[{"$minElement"=>1}, {"$maxElement"=>1}]]}}, {"cursor"=>"BtreeCursor user_id_1__type_1_processed_-1_uploaded_-1_image_device_1 multi", "n"=>28, "nscannedObjects"=>15094, "nscanned"=>15097, "indexBounds"=>{"user_id"=>[["512f7960534dcda22b000491", "512f7960534dcda22b000491"]], "_type"=>[["FacebookImageItem", "FacebookImageItem"], ["FoursquareImageItem", "FoursquareImageItem"], ["Image", "Image"], ["InstagramItem", "InstagramItem"], ["TwitterImageItem", "TwitterImageItem"]], "processed"=>[[{"$maxElement"=>1}, {"$minElement"=>1}]], "uploaded"=>[[{"$maxElement"=>1}, {"$minElement"=>1}]], "image_device"=>[[{"$minElement"=>1}, {"$maxElement"=>1}]]}}, {"cursor"=>"BtreeCursor user_id_1__type_1_when_tz_1 multi", "n"=>28, "nscannedObjects"=>28, "nscanned"=>15097, "indexBounds"=>{"user_id"=>[["512f7960534dcda22b000491", "512f7960534dcda22b000491"]], "_type"=>[["FacebookImageItem", "FacebookImageItem"], ["FoursquareImageItem", "FoursquareImageItem"], ["Image", "Image"], ["InstagramItem", "InstagramItem"], ["TwitterImageItem", "TwitterImageItem"]], "when_tz"=>[[{"$minElement"=>1}, {"$maxElement"=>1}]]}}, {"cursor"=>"BasicCursor", "n"=>0, "nscannedObjects"=>15097, "nscanned"=>15097, "indexBounds"=>{}}], "server"=>""}

Any idea how to get it to hit the indexes?

© Stack Overflow or respective owner

Related posts about mongodb

Related posts about indexes