Mongo DB compound index

Question

The situation: I have mongo db collection with user info. Among other fields it contains some user_email and user_login. These fields should be unique. During registration i want to check the uniqueness. I'm doing it by

db.users.find( { $or: [ { user_email: email }, { user_name: name } ] } )

If i will find something, I can assume that data is not unique.

So the problem here is that users table can be huge and i decided to create compound index, based on these two fields. Mongo docs says:

When using indexes with $or queries, each clause of an $or can use its own index. Consider the following query:

db.inventory.find( { $or: [ { quantity: { $lt: 20 } }, { price: 10 } ] } )

To support this query, rather than a compound index, you would create one index on quantity and another index on price:

db.inventory.createIndex( { quantity: 1 } )

db.inventory.createIndex( { price: 1 } )

MongoDB can use all but the geoHaystack index to support $or clauses.

So now I have 3 options:

compound index
two indexes
geoHaysatck index ( read about it a bit, seems that it can search for "closest" index entry. Not sure this is the way that I should use )

Could you guys give me some hints on index picking for this particular use case?

If you don't know what it is then you simply do not use it. I think the documentation is pretty clear on what works best, don't you? I mean you actually included the very part that confirms what to do in your question. — Neil Lunn
– Neil Lunn, Commented Jun 13, 2017 at 13:16

Vince Bowdren · Accepted Answer · 2017-06-13 20:36:01Z

2

You should use two separate indexes. If you create a compound index, it will be useful for at most one of the two $or options.

With your $or query, the two parts will act much like separate queries:

db.users.find( { user_email: email } )
db.users.find( { user_name: name } )

If you create a compound index, say on user_email then user_name, then the first query will be able to make effective use of this - but the second query will not. The only way to optimise both parts of the query is to have separate indexes, one on each field.

answered Jun 13, 2017 at 20:36

Vince Bowdren

9,2853 gold badges37 silver badges61 bronze badges

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

Collectives™ on Stack Overflow

Mongo DB compound index

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related