How to create an index in MongoDB with DateTimeOffset field generated by C#
361 views
Skip to first unread message
César Díaz
Sep 30, 2016, 3:49:10 AM9/30/16
to mongodb-user
I have an app written in C# that uses MongoDB 3.2
I´m using DateTimeOffset and it is stored like this:
"FechaOperacion" : [
NumberLong(636104520000000000),
NumberInt(120)
], I see that DateTimeOffset is stored as Ticks (long) and offset (int)
When I perform a search using MongoDB C# driver with query generated by Builders it looks like this:
{
"FechaOperacion" : {
"$gte" : [
NumberLong("636104448000000000"),
120
]
,
"$lte" : [
NumberLong("636107039990000000"),
120
]
}
}And it return results ok, but it scans entire collection (+250K documents)
If I create a compound index in "FechaOperacion.0" and "FechaOperacion.1" it doesn´t uses the index and return results, scanning entire collection:
{
"queryPlanner" : {
"plannerVersion" : NumberInt(1),
"namespace" : "DB55.OperacionDto",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"FechaOperacion" : {
"$lte" : [
NumberLong(636107039990000000),
NumberInt(120)
]
}
},
{
"FechaOperacion" : {
"$gte" : [
NumberLong(636104448000000000),
NumberInt(120)
]
}
}
]
},
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"$and" : [
{
"FechaOperacion" : {
"$lte" : [
NumberLong(636107039990000000),
NumberInt(120)
]
}
},
{
"FechaOperacion" : {
"$gte" : [
NumberLong(636104448000000000),
NumberInt(120)
]
}
}
]
},
"direction" : "forward"
},
"rejectedPlans" : [
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : NumberInt(917),
"executionTimeMillis" : NumberInt(389),
"totalKeysExamined" : NumberInt(0),
"totalDocsExamined" : NumberInt(251624),
"executionStages" : {
"stage" : "COLLSCAN",
"filter" : {
"$and" : [
{
"FechaOperacion" : {
"$lte" : [
NumberLong(636107039990000000),
NumberInt(120)
]
}
},
{
"FechaOperacion" : {
"$gte" : [
NumberLong(636104448000000000),
NumberInt(120)
]
}
}
]
},
"nReturned" : NumberInt(917),
"executionTimeMillisEstimate" : NumberInt(390),
"works" : NumberInt(251700),
"advanced" : NumberInt(917),
"needTime" : NumberInt(250708),
"needYield" : NumberInt(74),
"saveState" : NumberInt(2010),
"restoreState" : NumberInt(2010),
"isEOF" : NumberInt(1),
"invalidates" : NumberInt(0),
"direction" : "forward",
"docsExamined" : NumberInt(251624)
},
"allPlansExecution" : [
]
},
"serverInfo" : {
"host" : "HOST10",
"port" : NumberInt(27017),
"version" : "3.2.8",
"gitVersion" : "ed70e33130c977bda0024c125b56d159573dbaf0"
},
"ok" : 1.0
}I have tried creating an index in "FechaOperacion" field, but when I try to search with that kind of query it return no results
{
"queryPlanner" : {
"plannerVersion" : NumberInt(1),
"namespace" : "DB55.OperacionDto",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"FechaOperacion" : {
"$lte" : [
NumberLong(636107039990000000),
NumberInt(120)
]
}
},
{
"FechaOperacion" : {
"$gte" : [
NumberLong(636104448000000000),
NumberInt(120)
]
}
}
]
},
"winningPlan" : {
"stage" : "KEEP_MUTATIONS",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"FechaOperacion" : {
"$lte" : [
NumberLong(636107039990000000),
NumberInt(120)
]
}
},
{
"FechaOperacion" : {
"$gte" : [
NumberLong(636104448000000000),
NumberInt(120)
]
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"FechaOperacion" : NumberInt(1)
},
"indexName" : "FechaOperacion_1",
"isMultiKey" : true,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : NumberInt(1),
"direction" : "forward",
"indexBounds" : {
"FechaOperacion" : [
"[[], [ 636107039990000000, 120 ]]"
]
}
}
}
},
"rejectedPlans" : [
{
"stage" : "KEEP_MUTATIONS",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"FechaOperacion" : {
"$gte" : [
NumberLong(636104448000000000),
NumberInt(120)
]
}
},
{
"FechaOperacion" : {
"$lte" : [
NumberLong(636107039990000000),
NumberInt(120)
]
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"FechaOperacion" : NumberInt(1)
},
"indexName" : "FechaOperacion_1",
"isMultiKey" : true,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : NumberInt(1),
"direction" : "forward",
"indexBounds" : {
"FechaOperacion" : [
"[[ 636104448000000000, 120 ], BinData(0, ))"
]
}
}
}
}
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : NumberInt(0),
"executionTimeMillis" : NumberInt(0),
"totalKeysExamined" : NumberInt(0),
"totalDocsExamined" : NumberInt(0),
"executionStages" : {
"stage" : "KEEP_MUTATIONS",
"nReturned" : NumberInt(0),
"executionTimeMillisEstimate" : NumberInt(0),
"works" : NumberInt(2),
"advanced" : NumberInt(0),
"needTime" : NumberInt(0),
"needYield" : NumberInt(0),
"saveState" : NumberInt(0),
"restoreState" : NumberInt(0),
"isEOF" : NumberInt(1),
"invalidates" : NumberInt(0),
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"FechaOperacion" : {
"$lte" : [
NumberLong(636107039990000000),
NumberInt(120)
]
}
},
{
"FechaOperacion" : {
"$gte" : [
NumberLong(636104448000000000),
NumberInt(120)
]
}
}
]
},
"nReturned" : NumberInt(0),
"executionTimeMillisEstimate" : NumberInt(0),
"works" : NumberInt(1),
"advanced" : NumberInt(0),
"needTime" : NumberInt(0),
"needYield" : NumberInt(0),
"saveState" : NumberInt(0),
"restoreState" : NumberInt(0),
"isEOF" : NumberInt(1),
"invalidates" : NumberInt(0),
"docsExamined" : NumberInt(0),
"alreadyHasObj" : NumberInt(0),
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : NumberInt(0),
"executionTimeMillisEstimate" : NumberInt(0),
"works" : NumberInt(1),
"advanced" : NumberInt(0),
"needTime" : NumberInt(0),
"needYield" : NumberInt(0),
"saveState" : NumberInt(0),
"restoreState" : NumberInt(0),
"isEOF" : NumberInt(1),
"invalidates" : NumberInt(0),
"keyPattern" : {
"FechaOperacion" : NumberInt(1)
},
"indexName" : "FechaOperacion_1",
"isMultiKey" : true,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : NumberInt(1),
"direction" : "forward",
"indexBounds" : {
"FechaOperacion" : [
"[[], [ 636107039990000000, 120 ]]"
]
},
"keysExamined" : NumberInt(0),
"dupsTested" : NumberInt(0),
"dupsDropped" : NumberInt(0),
"seenInvalidated" : NumberInt(0)
}
}
},
"allPlansExecution" : [
{
"nReturned" : NumberInt(0),
"executionTimeMillisEstimate" : NumberInt(0),
"totalKeysExamined" : NumberInt(0),
"totalDocsExamined" : NumberInt(0),
"executionStages" : {
"stage" : "KEEP_MUTATIONS",
"nReturned" : NumberInt(0),
"executionTimeMillisEstimate" : NumberInt(0),
"works" : NumberInt(1),
"advanced" : NumberInt(0),
"needTime" : NumberInt(0),
"needYield" : NumberInt(0),
"saveState" : NumberInt(0),
"restoreState" : NumberInt(0),
"isEOF" : NumberInt(1),
"invalidates" : NumberInt(0),
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"FechaOperacion" : {
"$gte" : [
NumberLong(636104448000000000),
NumberInt(120)
]
}
},
{
"FechaOperacion" : {
"$lte" : [
NumberLong(636107039990000000),
NumberInt(120)
]
}
}
]
},
"nReturned" : NumberInt(0),
"executionTimeMillisEstimate" : NumberInt(0),
"works" : NumberInt(1),
"advanced" : NumberInt(0),
"needTime" : NumberInt(0),
"needYield" : NumberInt(0),
"saveState" : NumberInt(0),
"restoreState" : NumberInt(0),
"isEOF" : NumberInt(1),
"invalidates" : NumberInt(0),
"docsExamined" : NumberInt(0),
"alreadyHasObj" : NumberInt(0),
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : NumberInt(0),
"executionTimeMillisEstimate" : NumberInt(0),
"works" : NumberInt(1),
"advanced" : NumberInt(0),
"needTime" : NumberInt(0),
"needYield" : NumberInt(0),
"saveState" : NumberInt(0),
"restoreState" : NumberInt(0),
"isEOF" : NumberInt(1),
"invalidates" : NumberInt(0),
"keyPattern" : {
"FechaOperacion" : NumberInt(1)
},
"indexName" : "FechaOperacion_1",
"isMultiKey" : true,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : NumberInt(1),
"direction" : "forward",
"indexBounds" : {
"FechaOperacion" : [
"[[ 636104448000000000, 120 ], BinData(0, ))"
]
},
"keysExamined" : NumberInt(0),
"dupsTested" : NumberInt(0),
"dupsDropped" : NumberInt(0),
"seenInvalidated" : NumberInt(0)
}
}
}
},
{
"nReturned" : NumberInt(0),
"executionTimeMillisEstimate" : NumberInt(0),
"totalKeysExamined" : NumberInt(0),
"totalDocsExamined" : NumberInt(0),
"executionStages" : {
"stage" : "KEEP_MUTATIONS",
"nReturned" : NumberInt(0),
"executionTimeMillisEstimate" : NumberInt(0),
"works" : NumberInt(1),
"advanced" : NumberInt(0),
"needTime" : NumberInt(0),
"needYield" : NumberInt(0),
"saveState" : NumberInt(0),
"restoreState" : NumberInt(0),
"isEOF" : NumberInt(1),
"invalidates" : NumberInt(0),
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"FechaOperacion" : {
"$lte" : [
NumberLong(636107039990000000),
NumberInt(120)
]
}
},
{
"FechaOperacion" : {
"$gte" : [
NumberLong(636104448000000000),
NumberInt(120)
]
}
}
]
},
"nReturned" : NumberInt(0),
"executionTimeMillisEstimate" : NumberInt(0),
"works" : NumberInt(1),
"advanced" : NumberInt(0),
"needTime" : NumberInt(0),
"needYield" : NumberInt(0),
"saveState" : NumberInt(0),
"restoreState" : NumberInt(0),
"isEOF" : NumberInt(1),
"invalidates" : NumberInt(0),
"docsExamined" : NumberInt(0),
"alreadyHasObj" : NumberInt(0),
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : NumberInt(0),
"executionTimeMillisEstimate" : NumberInt(0),
"works" : NumberInt(1),
"advanced" : NumberInt(0),
"needTime" : NumberInt(0),
"needYield" : NumberInt(0),
"saveState" : NumberInt(0),
"restoreState" : NumberInt(0),
"isEOF" : NumberInt(1),
"invalidates" : NumberInt(0),
"keyPattern" : {
"FechaOperacion" : NumberInt(1)
},
"indexName" : "FechaOperacion_1",
"isMultiKey" : true,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : NumberInt(1),
"direction" : "forward",
"indexBounds" : {
"FechaOperacion" : [
"[[], [ 636107039990000000, 120 ]]"
]
},
"keysExamined" : NumberInt(0),
"dupsTested" : NumberInt(0),
"dupsDropped" : NumberInt(0),
"seenInvalidated" : NumberInt(0)
}
}
}
}
]
},
"serverInfo" : {
"host" : "HOST10",
"port" : NumberInt(27017),
"version" : "3.2.8",
"gitVersion" : "ed70e33130c977bda0024c125b56d159573dbaf0"
},
"ok" : 1.0
}I want to create an index that covers that kind of queries and don´t know how to do this Can anybody help me?
Thank you
Reply all
Reply to author
Forward
0 new messages