I am reading through mongo docs fro nodejs driver, particularly this index section https://www.mongodb.com/docs/drivers/node/current/fundamentals/indexes/#geospatial-indexes and it looks like all of the indexes that they mention are for sortable / searchable data. So I wanted to ask if I need indexes for following use case:

I have this user document structure

{
  email: string,
  version: number,
  otherData: ...
}

As far as I understand I can query each user by _id and this already has default unique index applied to it? I alos want to query user by email as well, so I created following unique index

collection.createIndex({ email: 1 }, { unique: true })

Is my understanding correct here that by creating this index I guarantee thaa:

1. Email is always unique
2. My queries like collection.findOne({email: '[email protected]'}) are optimised?

Next, I want to perform update operations on user documents, but only on specific versions, so:

collection.updateOne({email: '...', version: 2}, update)

What index do I need to create in order to optimise this query? Should I be somehow looking into compound indexes for this as I am now using email and version?

CodePudding user response：

1. Yes, the unique constraint happens at the db layer so by definition this will be unique, It is worth mentioning that this can affect insert/update performance as this check has to be executed on each of these instances - from my experience you only start feeling this overhead in larger scale ( hundreds of millions of documents in a single collection thousands of inserts a minutes ).

2. Yes. there is no other way to optimize this further.

What index do I need to create in order to optimise this query? Should I be somehow looking into compound indexes for this as I am now using email and version?

You want to create a compound index, the syntax will looks like this:

collection.createIndex({ email: 1, version: 1 }, { unique: true })

I will just say that by definition the (first) email index ensures uniqueness, so any additional filtering you add to the query and index will not really affect anything as there will always be only 1 of those emails in the DB. Basically why bother adding a "version" field to the query? if you need it for filtering that's fine but then you won't be needing to alter the existing index.