When it's advised NOT to created an index on a field?

[yaron_levi]

Hi (-:

I’ve always thought that you should absolutely never query mongo (in a live production app) without an index satisfying it (partially or full).

But if I have a collection that I need to query on a certain field not very frequently, but the writes are way more intensive.

To give some context. It’s a USERS collection saving user profiles. During login/Register we need to query the user by email, but this happens only once (or a little more) during the lifetime of the user. On the other hand, writes are being made all the time to that collections.

To emphasize the problem, let’s say we now need to support login by googleId, twitterId, and appleId. So I would need to create 3 more indexes just to find the correct user document during login. Those index would sure hurt regarding deletes,updates and inserts.

We checked the query, and without an index, it takes about 2-4 seconds. Around 1.4 million docs are in this collection. So regarding the user experience that would be fine for us.

In your mongo schema designs do you often go by this way of balancing between “must-have” indexed and “better to let it full scan” queries or do you absolutely satisfy any query with some index?

Any help would be much appreciated (-:

[Pavel_Duchovny]

Hi @yaron_levi ,

There are design patterns that allow you to minimize the index footprint/number even for a large number of query variations. For example the attribute pattern.

Building with Patterns: The Attribute Pattern | MongoDB Blog

Using this example you might have the following document:

{
 userId : "1",
"auth" : [ { "k" : "googleId" , "v" : "xxx" },
              { "k" : "twitterId" , "v" : "yyy" }]
...
}

The index is on { auth.k : 1, auth.v: 1}

Query can be

db.user.findOne({"auth" : {$elemMatch : { k: "googlId", v :"xxx"}}})

Ty

[yaron_levi]

@Pavel_Duchovny

A-M-A-Z-I-N-G I’ll take a look…