Multi index in same collection useful?

Hello, for each collection mongodb will create a default unique index based on _id, then I create another unique index on two fields of the collection, i.e. {country_code : 1 , creation_time : -1}, if I search the docs in collections, I would like to have data returned sorted by creation_time in DESC, but from the test result I found it is not always the expected: sometimes it returns in DESC, sometimes no. From the stackoverflow I found something like :
"What if an index is used?
If an index is used, documents will be returned in the order they are found (which does necessarily match insertion order or I/O order). If more than one index is used then the order depends internally on which index first identified the document during the de-duplication process."
so my question is, how can I make the index of {country_code : 1 , creation_time : -1} firstly be used when I search the data?

thanks,

James

Hi @Zhihong_GUO,

What query are you running with your sort? The query is important because it’s a big factor in how the database picks a candidate index.

The index { country_code: 1, creation_time: 1 } cannot be used to help with sorting unless the query also includes an equality on country_code like this:
db.collection.find({ country_code: "us" }).sort({ creation_time: 1 })

Any query that doesn’t include country_code would prevent the index from being used to sort creation_time.

I hope that helps!

Thanks,

Justin

1 Like

Hello Justin,

Yes I will search docs by country_code and hope to have docs sorted by creation_time, your answer exactly give me help.

Thanks a lot,

James