How to Optimizing MongoDB Compound Indexes

Working with Data
1

I’m using MongoDB version 3.4.6. I have a collection with the following two indexes:

"key": {
        "create_time": -1,
        "user_id": 1
    },
"name": "create_time_-1_user_id_1"


"key": {
        "user_id": 1
        "create_time": -1,
    },
"name": "user_id_1_create_time_-1"

then the query statement is

queryBson := bson.M{
    "user_id": bson.M{
        "$in": []int{1,2,3,4},
    },
    "create_time": bson.M{
        "$lte": "time",
    },
}

we found that “user_id” $in with more than 300 user_id,mongo will use the index “user_id_1_create_time_-1” which cause slow query

in order to avoid this slow query,when the “user_id” $in with more than 300 user_id,we use the hint index “create_time_-1_user_id_1”

so I want to ask, why mongo choose the wrong index?

If a query can be satisfied by multiple indexes defined in the collection, MongoDB will test all the applicable indexes in parallel. can anybody tell me the underlying principle? I want to understand deeper how mongo choose index when a query can be satisfied by multiple indexes defined in the collection

the other question,is necessary create two indexs “create_time_-1_user_id_1” and “user_id_1_create_time_-1”? this wasting memory space

If we could only create one index，which index should we create?

2

Hello @scau_zhangzhimu_scau_zhangzhimu, welcome to the MongoDB forum!

When using compound indexes, one of the factors you need to consider is this: Create Queries that Ensure Selectivity. This will help decide what the order of the fields needs to be.

3

Thanks. Please clarify more.
1.how mongo choose index when a query can be satisfied by multiple indexes defined in the collection? I want to understand deeper
2.query statement is user_id and create_time,we can create index “create_time_-1_user_id_1” or “user_id_1_create_time_-1” or both,which one is better? in Production Environment，we create the index “user_id_1_create_time_-1”,but when the query statement user_id $in more than 300,it cause slow query,so we have to create the index “create_time_-1_user_id_1” and use hint

4

As I had mentioned before, one of the aspects to consider in determining the compound index is the Selectivity (my previous post has the link to the topic).

Another tool useful is the: Compare Performance of Indexes.

As such usage of hint is not recommended, in general. The query performance is dependent upon various factors - the size and amount of data, the indexes, the query operation, hardware factors (like memory, CPU, network, etc.), other processes running on your system, etc.

I am not familiar with your work environments. The information I have included so far is useful for you to analyze your queries - compare the performances and do further optimizations. Please refer these for additional information:


The MongoDB query optimizer processes queries and chooses the most efficient query plan for a query given the available indexes. …

2 Likes