2

I've been working on an issue in a MongoDB database and I'm confused about its choices on indexes.

I have a collection with roughly 35 millions records, and 2 compound indexes. Both of the indexes fit in the Mongo cache.

The indexes looked like this when I went to investigate the problem:

    {
    "v" : 2,
    "key" : {
        "recType" : 1,
        "tstamp" : -1
    },
    "name" : "recType_1_tstamp_-1",
    "ns" : "example.objs",
    "background" : true
},
{
    "v" : 2,
    "key" : {
        "recType" : 1,
        "data.time" : -1
    },
    "name" : "rectype_1_data.time_-1",
    "ns" : "example.objs",
    "background" : true
}

We had some queries hanging. The queries looked like this:

db.objs.find({ "recType": "someType", "tstamp": { $gte: NumberLong(1529078246476) } }).count()

These would run for hours without returning. By checking out the currentOp, I noted that the query was using the index called

rectype_1_data.time_-1

I thought this was odd considering there is a compound index on the fields specified in the find. I then ran the query with a hint:

db.objs.find({ "recType": "someType", "tstamp": { $gte: NumberLong(1529078246476) } }).hint("recType_1_tstamp_-1").count()

This one ran in about 1 second. So my first question is, why would Mongo choose the wrong index here? Is there something about the query that confuses it? Are there some internal stats that can maybe be cleared?

Next, since I didn't want to run it with a hint all the time, I created a similar index with the second key in the other direction:

    {
    "v" : 2,
    "key" : {
        "recType" : 1,
        "tstamp" : 1
    },
    "name" : "recType_1_tstamp_1",
    "ns" : "example.objs",
    "background" : true,
}

Now, this index is properly selected for the above query (without needing a hint).

My second question is, does the ordering of the second key in index of this specific key and query matter? My understanding is that it shouldn't matter.

I'm very unsatisfied with leaving this as the solution in my database without understanding why it is making these choices.

Rich Churcher
  • 7,361
  • 3
  • 37
  • 60
Erix
  • 7,059
  • 2
  • 35
  • 61
  • 1
    Version? Also would not hurt to show the output of `db.objs.explain({ "verbosity": "allPlansExecution" }).find({ "recType": "someType", "tstamp": { $gte: NumberLong(1529078246476) } }).count()` to show the execution stats and of course the considered plans of the query planner. – Neil Lunn May 19 '19 at 01:52
  • 1
    I think you've answered your own question: for single field indexes (or the first field in a compound index) the [sort order](https://docs.mongodb.com/manual/core/index-compound/#sort-order) doesn't matter. For subsequent fields in a compound index, the sort order definitely matters for index traversal with range queries or sorts. The most useful detail to review would be the results of `explain('allPlansExecution')` so you can compare the work involved for different candidate indexes. – Stennie May 19 '19 at 03:31

0 Answers0