CODEWITHSUNDEEP
mongodbindexing
Sklavit
Sklavit

Reputation: 2341

How to select MongoDB indexes for complex queries

Answering this question, probably, involves multiple issues, so it should be fun :) So, I have

  1. a collection with fields {a, a1, s, i, j}.

  2. field a1 always has the same value as a

  3. value of field s is always the same (static)

  4. I will use only two types of queries:

  5. find(a=a, a1=a, s=s, i=i)

  6. find(a=a, a1=a, s=s, j=j)

Which index(es) should be created and why?

Upvotes: 0

Views: 55

Answers (1)

Derek Menénedez
Derek Menénedez

Reputation: 2367

You must create two compound indexes for the best performance

one should be { a, a1, s, i } the other should be { a, a1, s, j }

Why? For best performance, you can include all the fields of the most used queries, in this case, the query .5 and .6

An essential fact of the compound indexes is that you can use the index if your query has attributes that are part of an index prefix. So, if you have a compounded index (a,b,c,d) a prefix can be:

  • a
  • a, b
  • a, b, c
  • a, b, c, d

so if you have the index (a,b,c,d) and you have a query for find(a=a) the query will use the index.

if you have the index (a,b,c,d) and you have a query for find(b=b, c=c) the query will not use the index.

if you have the index (a,b,c,d) and you have a query for find(a=a, c=c) the query will use the index but not with the best performance because will do a full scan in a way to do the comparison for the c attribute.

The order of the find operation doesn't matter but the order of the compound index matters.

Compound indexes: https://www.mongodb.com/docs/manual/core/index-compound/

Upvotes: 1

Related Questions