Discussion:
Distinct on array of embedded documents doesn't use index?
(too old to reply)
Adam Dohnal
2016-04-24 14:03:39 UTC
Permalink
Hello,

let's say I have collection of documents like this, so there is array field
with embedded document

{
_id: 1,
item: "abc",
stock: [
{ size: "S", color: "red", quantity: 25 },
{ size: "S", color: "blue", quantity: 10 },
{ size: "M", color: "blue", quantity: 50 }
]
}


I have created a multikey index on "stock.size", but when I run
db.items.distinct("stock.size") the whole collections is scanned. This is
the result of db.runCommand({distinct: "items", key: "stock.size"})


"stats" : {

"n" : 1730969,

"nscanned" : 0,

"nscannedObjects" : 1730969,

"timems" : 3178,

"planSummary" : "COLLSCAN"

}

What can I do in order to use that index?
--
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.org/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 mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
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/33b743ef-47eb-415f-958a-01ec9a4d3d02%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Rhys Campbell
2016-04-25 07:59:07 UTC
Permalink
Can you show us the output of...

db.items.getIndexes();

and

db.items.distinct("stock.size");
--
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.org/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 mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
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/32820ba2-a497-4036-ba82-3539bd787afc%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Rhys Campbell
2016-04-25 08:15:21 UTC
Permalink
Rather...

db.items.distinct("stock.size").explain();
Post by Rhys Campbell
Can you show us the output of...
db.items.getIndexes();
and
db.items.distinct("stock.size");
--
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.org/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 mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
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/9da62b7f-ec7e-48f1-b653-d2117de09fed%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Adam Dohnal
2016-04-25 08:21:13 UTC
Permalink
db.items.getIndexes()

[

{

"v" : 1,

"key" : {

"_id" : 1

},

"name" : "_id_",

"ns" : "local.items"

},

{

"v" : 1,

"key" : {

"stock.size" : 1

},

"name" : "stock.size_1",

"ns" : "local.items"

}

]

db.items.distinct("stock.size")

[ "M", "S" ]

db.items.distinct("stock.size").explain();

2016-04-25T10:19:42.731+0200 E QUERY TypeError: Object M,S has no method
'explain'

at (shell):1:33
--
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.org/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 mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
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/6217898d-abbe-4c44-b987-86029ed5e8b7%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Rhys Campbell
2016-04-25 11:28:09 UTC
Permalink
OK, so no explain for this method then!

This looks like the reason...

any of the indexed fields in any of the documents in the collection
includes an array. If an indexed field is an array, the index becomes a multi-key
index
<https://docs.mongodb.org/manual/core/index-multikey/#index-type-multikey> and
cannot support a covered query.

https://docs.mongodb.org/manual/core/query-optimization/#covered-queries

Have you tried the aggregate framework? Perhaps you'll have between luck
with that to avoid this. $match and then grouping to a distinct list will
probably use indexes.
--
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.org/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 mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
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/432cbfa1-c80e-4f9a-b5ea-0001d537afd4%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Adam Dohnal
2016-04-25 12:25:14 UTC
Permalink
I have tried aggregation framework, but when I want distinct values across
whole collection, I can't use $match stage and $group stage seems that
doesn't use indexes as well.

db.items.aggregate([{$unwind: "$stock"}, {$group: {_id: "$stock.size"}}], {
explain: true})
{
"stages" : [
{
"$cursor" : {
"query" : {

},
"fields" : {
"stock" : 1,
"_id" : 0
},
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "local.items",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [ ]
},
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"$and" : [ ]
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
}
}
},
{
"$unwind" : "$stock"
},
{
"$group" : {
"_id" : "$stock.size"
}
}
],
"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.org/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 mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
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/b5bed2ab-f660-49fd-8ff2-9e33b4c6a7f2%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Asya Kamsky
2016-04-26 00:52:09 UTC
Permalink
You can vote up this ticket: https://jira.mongodb.org/browse/SERVER-13298

Though this is also related to https://jira.mongodb.org/browse/SERVER-2104
Post by Adam Dohnal
Hello,
let's say I have collection of documents like this, so there is array field
with embedded document
{
_id: 1,
item: "abc",
stock: [
{ size: "S", color: "red", quantity: 25 },
{ size: "S", color: "blue", quantity: 10 },
{ size: "M", color: "blue", quantity: 50 }
]
}
I have created a multikey index on "stock.size", but when I run
db.items.distinct("stock.size") the whole collections is scanned. This is
the result of db.runCommand({distinct: "items", key: "stock.size"})
"stats" : {
"n" : 1730969,
"nscanned" : 0,
"nscannedObjects" : 1730969,
"timems" : 3178,
"planSummary" : "COLLSCAN"
}
What can I do in order to use that index?
--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
https://docs.mongodb.org/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
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/33b743ef-47eb-415f-958a-01ec9a4d3d02%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
--
Asya Kamsky
Lead Product Manager
MongoDB
Download MongoDB - mongodb.org/downloads
Free MongoDB Monitoring - cloud.mongodb.com
Free Online Education - university.mongodb.com
Get Involved - mongodb.org/community
We're Hiring! - https://www.mongodb.com/careers
--
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.org/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 mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
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/CAOe6dJAO%2Bku4OJokwaxFwoPDF%2B-vpny7GjK0U2v3_B8OCR-QFw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
Continue reading on narkive:
Loading...