How does MongoDB treat find().sort() queries with respect to single and compound indexes?

Question

So I have a typical find().sort() query that I run on my mongo collection.

db.collection.find({field1:1}).sort({field2:1})

Now, say I have three indexes on this collection:

single index on field1
single index on field2
compound index on field1 and field2 - {field1:1,field2:1}

Now my question, how does mongoDB treat the above query? What are the indexes that will be used in a query like that- two single indexes or the one compound index?

If I remove the compound index, does it in fact make use of the two single indexes but slowing down?

very helpful: docs.mongodb.org/manual/tutorial/sort-results-with-indexes — Disposer
– Disposer, Commented Jan 2, 2015 at 8:00
@Disposer so it uses a compound index right...is it correct to expect the query to use the single indexes and to get slow if there's no compound index? — gravetii
– gravetii, Commented Jan 2, 2015 at 8:08

Disposer · Accepted Answer · 2015-01-02 08:29:21Z

3

If I got your point, this might help:

Assuming you have these documents for sample

{
    field1 : 1,
    field2 : 2,
},
{
    field1 : 2,
    field2 : 3,
},
{
    field1 : 1,
    field2 : 4,
}

Step 1: you have index just for filed1 (name of index field1_1)}: perform the : db.test3.find({field1:1}).sort({field2:1})

the mongo uses field1_1 index to search in document. the result of .explain() is:

"cursor" : "BtreeCursor field1_1",
"isMultiKey" : false,
"n" : 2,
"nscannedObjects" : 2,
"nscanned" : 2,

Step 2: add your compound index, name it field1_1_field2_1, now you have 2 index for field 1.

perform find().sort() query, you will have

"cursor" : "BtreeCursor field1_1_field2_1",
"isMultiKey" : false,
"n" : 2,
"nscannedObjects" : 2,
"nscanned" : 2,

Concolusion:

if you use db.test3.find({field1:1}).sort({field2:1}), the mongo will use field1_1_field2_1 index.

if you use db.test3.find({field1:1}), the mongo will use field1_1 index.

I your case, If you have just field1_1_field2_1 index and you are performing db.test3.find({field1:1}), the mongo will use field1_1_field2_1 index as well.

answered Jan 2, 2015 at 8:29

Disposer

6,3915 gold badges34 silver badges39 bronze badges

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

2 Comments

Markus W Mahlberg Over a year ago

Which is a perfect description why it does not make sense to have single field indices in case the field already is indexed in a compound index.

gravetii Over a year ago

@MarkusWMahlberg true, though I was more concerned with not having a compound index :-)

Collectives™ on Stack Overflow

How does MongoDB treat find().sort() queries with respect to single and compound indexes?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related