1

I'm doing queries like this:

.find({name: "bob", gender: "male"})
.find({name: "alice"})

I created 2 indexes, one with {"name": 1}, and the other {"name": 1, "gender": 1}

Does creating 2 indexes make sense here? Is it a waste of space to do so? Can I for example, get rid of the name index?

Harry
  • 52,711
  • 71
  • 177
  • 261
  • The larger concern would be is the "compound" index actually needed? It may well be in your case and such questions as "is it needed" are really subjective to your actual usage. I will add that "logically" such an index should in fact be ordered as `{ gender: 1, name: 1 }` considering that gender "usually" ( not judging here ) refers to a binary choice, and would therefore the be greater limiting factor and make more sense to the be "prefix" of such an index. Of course changing that order as such would mean that simply `{ name: 1 }` when used solely for that purpose, would be a requirement. – Neil Lunn Oct 27 '17 at 00:18
  • Kind of strongly related: [How does the order of compound indexes matter in MongoDB performance-wise?](https://stackoverflow.com/a/33546159/2313887). It's mostly direct manual quotes, but we are talking about "name" and "sex" specifically and exactly within the answer. Most important is "Cardinality", which is what I just described basically as being the key point of consideration. – Neil Lunn Oct 27 '17 at 00:24
  • @NeilLunn Thanks interesting question you linked. – Harry Oct 27 '17 at 23:06
  • 1
    The related answer suggested in a comment above is definitely applicable to your use case, although the first comment incorrectly reverses the recommended field order. Fields with high cardinality (unique values) are more effective at narrowing the search space so should come earlier in the index. Fields with boolean or low cardinality values are generally not helpful as the leading elements in an index, so the recommended compound order would be `{name:1, gender:1}` in your case. – Stennie Oct 29 '17 at 03:23
  • Indexes that are a leftmost prefix of another compound index are indeed superfluous: a `{name:1,gender:1}` index can efficiently satisfy the same queries as `{name:1}`. You can indeed drop the `{name:1}` index to improve efficiency. – Stennie Oct 29 '17 at 03:25

1 Answers1

1

yes, it's a waste of memory, all you need is a compound index of 'name' and 'gender'.

db.table.createIndex( { "name": 1, "gender": 1 } )

This allows you both options. You can query on just name, and you also can query on category combined with item. A single compound index on multiple fields can support all the queries that search a “prefix” subset of those fields.

ref: https://docs.mongodb.com/manual/tutorial/create-indexes-to-support-queries/#create-compound-indexes-to-support-several-different-queries

Despagito
  • 326
  • 3
  • 8