MongoDB find with sort performance

[Mykyta_Bezverkhyi]

Hey guys, I have a performance issues while trying to find and sort a big amount of data (more than 3m of records, but limit is 10k).
So I have the following request:

db.getCollection(“events”).find({
“field1”: {“$in”: [“value1”, null]},
“field2”: “value2”,
“field3”: {
“$in”: [“type1”, “type2”, “type3”]
}
}).sort({“timestamp”: -1}).explain(“executionStats”)

I have a single field index of {“timestamp”: -1} and executionTimeMillis is around 11k

I tried to create compound index of {“field1”: 1, “field2”: 1, “field3”: 1}, but as I can see this index is not used cos of sort, so I tried to create another one as {“field1”: 1, “field2”: 1, “field3”: 1, “timestamp”: -1}, but it gives me executionTimeMillis around 8k and it’s a bit better (SORT_MERGE is used), but still it’s 3k without sort.

Any tricks on how to use sortable field in compound index to improve performance here?

[Pavel_Duchovny]

Hi @Mykyta_Bezverkhyi,

Welcome to MongoDB community!

The trick with indexing is the order of the fields should follow one thumb rule and it is Equality , Sort , Range.

The $in operator is actually considered range so I would try the following index:
{ field2 : 1, timestamp : -1, field1: 1, field3: 1}

Performance Best Practices: Indexing | MongoDB Blog

If this does not help please provide execution Stats plan.

Best
Pavel

[Mykyta_Bezverkhyi]

Thanks for your answer @Pavel_Duchovny, it answers some of my questions, but I already tried some configurations, and I got the following results:

Documents Returned:9420
Index Keys Examined:1610000
Documents Examined:9420
Actual Query Execution Time (ms):10664
Sorted in Memory:no
Query used the following index:
field2_1_timestamp_-1_field1_1_field3_1

But I managed to improve performance by the following:

Documents Returned:9420
Index Keys Examined:158261
Documents Examined:9420
Actual Query Execution Time (ms):603
Sorted in Memory:no
Query used the following index:
field1_1_field2_1_timestamp_-1_field3_1

Any explanations on why it could be faster? For some reasons I got better results having field1 as the first in my index.

[Pavel_Duchovny]

@Mykyta_Bezverkhyi, I assume that field1 which has 1 value (nulls are not indexed) can be used as equility by the engine.

Therefore fields 1,2 should be.before the sort…

Thanks