MongoDB - distinct with query doesn't use indexes

Question

Using Mongo 3.2.

Let's say I have a collection with this schema:

{ _id: 1, type: a, source: x },
{ _id: 2, type: a, source: y },
{ _id: 3, type: b, source: x },
{ _id: 4, type: b, source: y }

Of course that my db is much larger and with many more types and sources.

I have created 4 indexes combinations of type and source (even though 1 should be enough):

{type: 1}
{source: 1},
{type: 1, source: 1},
{source: 1, type: 1}

Now, I am running this distinct query:

db.test.distinct("source", {type: "a"})

The problem is that this query takes much more time that it should take. If I run it with runCommand:

db.runCommand({distinct: 'test', key: "source", query: {type: "a"}})

this is the result i get:

{
    "waitedMS": 0,
    "values": [
        "x",
        "y"
    ],
    "stats": {
        "n": 19400840,
        "nscanned": 19400840,
        "nscannedObjects": 19400840,
        "timems": 14821,
        "planSummary": "IXSCAN { type: 1 }"
    },
    "ok": 1
}

For some reason, mongo use only the type: 1 index for the query stage. It should use the index also for the distinct stage. Why is that? Using the {type: 1, source: 1} index would be much better, no? right now it is scanning all the type: a documents while it has an index for it.

Am I doing something wrong? Do I have a better option for this kind of distinct?

TomG · Accepted Answer · 2016-03-15 12:13:58Z

1

As Alex mentioned, apparently MongoDB doesn't support this right now. There is an open issue for it: https://jira.mongodb.org/browse/SERVER-19507

answered Mar 15, 2016 at 12:13

TomG

2,5695 gold badges27 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alvin Wong Over a year ago

from the issue,looks like it should have been implemented in 3.4?

Robert Over a year ago

@Alvin Wong You are right, thankfully this feature has been implemented in Mongo 3.4.

Alex Blex · Accepted Answer · 2016-03-15 10:43:31Z

-2

Just drop first 2 indexes. You don't need them. Mongo can use {type: 1, source: 1} in any query that may need {type: 1} index.

edited Mar 15, 2016 at 10:43

answered Mar 15, 2016 at 10:01

Alex Blex

37.3k7 gold badges53 silver badges87 bronze badges

4 Comments

TomG Over a year ago

It doesn't change the results. Yea it will use the {type: 1, source: 1} index, but only for the query stage, not for the distinct stage, so the time and the resutls are the same

Alex Blex Over a year ago

It does not use indexes for distinct stage, unless index start from the key. In your case {source: 1} can be used for DISTINCT_SCAN if query is empty.

TomG Over a year ago

I know, but this is not what I want. I want to distinct with a query, so it should use DISTINCT_SCAN on {type: 1, source: 1} index

Alex Blex Over a year ago

Not doable atm. You can vote for the ticket jira.mongodb.org/browse/SERVER-19507, but I don't think it's going to be resolved any soon.

Collectives™ on Stack Overflow

MongoDB - distinct with query doesn't use indexes

2 Answers 2

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related