CODEWITHSUNDEEP
mongodbindexing
Ivan Lymar
Ivan Lymar

Reputation: 2293

Mongo DB compound index

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:

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

Upvotes: 0

Views: 95

Answers (1)

Vince Bowdren
Vince Bowdren

Reputation: 9208

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:

  1. db.users.find( { user_email: email } )
  2. 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.

Upvotes: 2

Related Questions