Subject: [mongodb-user] Issue with Multi-Key queries and
Regex



Greetings.
I have a multi-key index, and I am attempting to query on the first two values in the index.  First value in the index is being queried using a Regex.  This seems to be suppressing the use of the rest of the keys in this index.  Considering the size of the actual datasets, etc., this is "not good".
The "real life" that this comes from, the first field is part of the shard key, and is used as a field in Compound Indexes for queries.  Is there any way with can do a "starts-with" type query w/o suppressing the the use of the rest of the fields in the Index?
NOTE: The database this was pulled from is loaded with <1% of the actual data load, only 32million documents (about 6hrs of data under low-medium load, and full data set is 90 days under medium-high load)
Regards,Steve
(Explain plan -- field names, table names, and index names have been changed to "protect the innocent").db.showAndTell.find({"field1": { $regex: /^cv-abc-1/ }, "field2" : "cv-def-fghijk"}).explain("executionStats"){ "queryPlanner" : { "plannerVersion" : 1, "namespace" : "ExampleDB.showAndTell", "indexFilterSet" : false, "parsedQuery" : { "$and" : [ { "field2" : { "$eq" : "cv-def-fghijk" } }, { "field1" : /^cv-abc-1/ } ] }, "winningPlan" : { "stage" : "FETCH", "filter" : { "field2" : { "$eq" : "cv-def-fghijk" } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "field1" : 1, "field2" : 1, "port.pv" : 1, "clientPort.pv" : 1, "protocol.name" : 1, "recordTimes.eventTime" : 1 }, "indexName" : "showAndTellIndex1", "isMultiKey" : true, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 1, "direction" : "forward", "indexBounds" : { "field1" : [ "[\"cv-abc-1\", \"cv-hst-i\")", "[/^cv-abc-1/, /^cv-abc-1/]" ], "field2" : [ "[MinKey, MaxKey]" ], "eventTime" : [ "[MinKey, MaxKey]" ] } } }, "rejectedPlans" : [ ] }, "executionStats" : { "executionSuccess" : true, "nReturned" : 315816, "executionTimeMillis" : 1090374, "totalKeysExamined" : 2644201, "totalDocsExamined" : 1059450, "executionStages" : { "stage" : "FETCH", "filter" : { "field2" : { "$eq" : "cv-def-fghijk" } }, "nReturned" : 315816, "executionTimeMillisEstimate" : 10905, "works" : 2750747, "advanced" : 315816, "needTime" : 2328385, "needYield" : 106545, "saveState" : 108266, "restoreState" : 108266, "isEOF" : 1, "invalidates" : 0, "docsExamined" : 1059450, "alreadyHasObj" : 0, "inputStage" : { "stage" : "IXSCAN", "nReturned" : 1059450, "executionTimeMillisEstimate" : 5666, "works" : 2644202, "advanced" : 1059450, "needTime" : 1584751, "needYield" : 0, "saveState" : 108266, "restoreState" : 108266, "isEOF" : 1, "invalidates" : 0, "keyPattern" : { "field1" : 1, "field2" : 1, "eventTime" : 1 }, "indexName" : "showAndTellIndex1", "isMultiKey" : true, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 1, "direction" : "forward", "indexBounds" : { "field1" : [ "[\"cv-abc-1\", \"cv-hst-i\")", "[/^cv-abc-1/, /^cv-abc-1/]" ], "field2" : [ "[MinKey, MaxKey]" ], "eventTime" : [
"[MinKey, MaxKey]" ] }, "keysExamined" : 2644201, "seeks" : 2, "dupsTested" : 2644200, "dupsDropped" : 1584750, "seenInvalidated" : 0 } } }, "serverInfo" : { "host" : "thor", "port" : 27017, "version" : "3.4.1", "gitVersion" : "5e103c4f5583e2566a45d740225dc250baacfbd7" }, "ok" : 1}> 
 

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/547fa26f-c134-468b-adf9-87848fff9e51%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.



Programming list archiving by: Enterprise Git Hosting