How to index queries with $nin and $ne filters?

[kllam55]

Our use cases contain filter conditions that use $nin and $ne on
various fields. We tried including these keys in our multi-key
indexes; however, we find that the queries do not have much
improvement with these keys added or not since the response time does
not change and the nscannedObjects values do not decreased much when
we try running the query with .explain()

Any insights/tips into indexing these type of use cases/keys is
greatly appreciate it!

Thanks
King Lam

[Nat]

Can you show your query and explain?

[kllam55]

Yes. Here is the query with explain (initial combo indexes):
-------------------------
db.objects.find({ locale: "us", state: "published", series: { $nin:
[ "sponsor" ] }, restricted: { $ne: true }, tags: { $all:
[ "gameplay", "ipad2" ] } }).explain()

{
"cursor" : "BtreeCursor locale_1_state_1_tags_1",
"nscanned" : 58213,
"nscannedObjects" : 58213,
"n" : 23,
"millis" : 10301,
"indexBounds" : {
"locale" : [
[
"us",
"us"
]
],
"state" : [
[
"published",
"published"
]
],
"tags" : [
[
"gameplay",
"gameplay"
]
]
}

}


Here is the query with explain (second combo indexes added for the
$nin and $ne filtered keys):
-----
db.objects.find({ locale: "us", state: "published", series: { $nin:
[ "sponsor" ] }, restricted: { $ne: true }, tags: { $all:
[ "gameplay", "ipad2" ] } }).explain()

{
"cursor" : "BtreeCursor
locale_1_state_1_series_1_restricted_1_tags_1",
"nscanned" : 58213,
"nscannedObjects" : 58213,
"n" : 23,
"millis" : 9898,
"indexBounds" : {
"locale" : [
[
"us",
"us"
]
],
"state" : [
[
"published",
"published"
]
],
"series" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
],
"restricted" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
],
"tags" : [
[
"gameplay",
"gameplay"
]
]
}
}

-----

It seems like the series and restricted key range do not restrict
anything due to the $ne and $nin filters.

Please let me know if more info is needed.

[Eliot Horowitz]

Its hard to make use of an index efficiently fot $nin or $ne
locale,state,tags is definitely a good prefix.
adding series, restricted won't help limit ranges to scan, but it
means you can use the index as a covered index, so it should be
faster.

On Fri, Mar 18, 2011 at 12:52 AM, kllam55 <kll...@gmail.com> wrote:
> Yes.  Here is the query with explain (initial combo indexes):
> -------------------------
>> db.objects.find({ locale: "us", state: "published", series: { $nin: [ "sponsor" ] }, restricted: { $ne: true }, tags: { $all: [ "gameplay", "ipad2" ] } }).explain()
> {
>        "cursor" : "BtreeCursor locale_1_state_1_tags_1",
>        "nscanned" : 58213,
>        "nscannedObjects" : 58213,
>        "n" : 23,
>        "millis" : 10301,
>        "indexBounds" : {
>                "locale" : [
>                        [
>                                "us",
>                                "us"
>                        ]
>                ],
>                "state" : [
>                        [
>                                "published",
>                                "published"
>                        ]
>                ],
>                "tags" : [
>                        [
>                                "gameplay",
>                                "gameplay"
>                        ]
>                ]
>        }
> }
>
> Here is the query with explain (second combo indexes added for the
> $nin and $ne filtered keys):
> -----

>  db.videos.find({ locale: "us", state: "published", series: { $nin:

> --
> 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.
> For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.
>
>