CODEWITHSUNDEEP
mongodbindexing
MK Yung
MK Yung

Reputation: 4581

Can a compound index be used in searching a single field in MongoDB?

If I create the following compound index:

{'username': 1, 'uid': 1}

Can this index be used if I only search with uid, like db.users.find({'uid': '12345'}) ?

Because generating two separate single indexes seems to use more memory.

Upvotes: 5

Views: 2620

Answers (4)

Hardik Mandankaa
Hardik Mandankaa

Reputation: 3376

If your index: {a:1,b:1,c:1}.

Then, it will apply for below conditions:

db.coll.find({a:'xxx'})
db.coll.find({b:'yyyy'})
db.coll.find({c:'zzz'})
db.coll.find({a:'xxx',c:'zzz'})
db.coll.find({a:'xxx',b:'yyy'})
db.coll.find({a:'xxx',b:'yyy',c:'zzz'})
db.coll.find({b:'yyy',a:'xxx',c:'zzz'})
db.coll.find({b:'yyy',c:'zzz',a:'xxx'})

It will not work in the below conditions:

db.coll.find({b:'yyy',c:'zzz'})
db.coll.find({b:'yyy'})
db.coll.find({c:'zzz'})

Indexes are working only if your index prefix is in your search query. Order doesn't matter.

Upvotes: 0

Grzegorz Wieczorek
Grzegorz Wieczorek

Reputation: 61

The answer is no, mongo will not use this compound index when you find by uid.

Your index will be used when you will try to find by username or by pair username and uid. It is well described in mongo documentation: https://docs.mongodb.com/manual/core/index-compound/#prefixes

If you prefer searching by uid instead of username, you should switch order in your compound index.

If you do not search by username and uid simultaneously, best option is to drop this compound index and create two separated indexes.

Upvotes: 0

Anand Jayabalan
Anand Jayabalan

Reputation: 12904

As long as you are querying by fields that are a left subset of the indexed fields, MongoDB will automatically use the index. That is, if you have an index like {a:1, b:1, c:1}, all the 3 queries db.coll.find({a:"xxx"}), db.coll.find({a:"xxx", b:"yyy"}) and db.coll.find({a:"xxx", b:"yyy", c:"zzz"}) will make use of this index, assuming there are no other indexes.

Upvotes: 11

Sammaye
Sammaye

Reputation: 43884

It can by using hint() however, the optimiser will not pick this index by default because it searches by prefix and uid is not a prefix of {username, uid}.

As for using the index when searching, it will scan all username values and then scan for the uid in each username.

This can sometimes be a good thing, especially if you want to sort those uid values by username, since scanandorder will be false but if you are not expecting to sort by the username fields then you could find that it could get a bit unperformant.

Upvotes: 1

Related Questions