Simple Query Performance

Hey Guys,

Very simple question. I have the following schema:

{
_id: ObjectId,
email: String,
name: String,
isBlocked: Boolean
}

On this Schema, I have the following UNIQUE Index
{
email: 1
}

Which performance is better:

db.find({email: ‘abc@email.com’, isBlocked: false})
// Including isBlocked, which is not indexed, as part of the query

or

const item = await db.find({email: ‘abc@email.com’});
const isBlocked = item?.isBlocked || false;
if (isBlocked)
return null;

(Note: this is just an example, I know it would be better to have isBlocked in the index as well, however indexing everything is also a cost to the DB, so I was wondering in this kind of scenario, it would be better to query by the index, and add a business rule to validate the outcome)

  1. Not having isBlocked in the index implies that the document would need to be fetched to determine if it is returned or not.

  2. Not including isBlocked in your query implies that the document would need to be fetch so that it is returned to you before you determine if you return nul or not.

2 is more work and more data over the network.

A third alternative, is to have a partial index with isBlocked as part of expression.

1 Like