Best Index and Queries for My Collection


I have a collection that has these fields

“field1” : “abc”,
“field2” : 1234,
“field3” : “sadas”,
“field4” : { “nestedField”: 1234},
“occurred_at” : 111111111

I may have varying queries like →

SELECT * FROM myCollection WHERE field1 = "abcdef"
SELECT * FROM myCollection WHERE field2 = 12345 and field3 = "asdasdas" order by occurred_at DESC
SELECT * FROM myCollection WHERE field4.nestedField = 42343 order by occurred_at DESC
SELECT field2 FROM myCollection WHERE field3 = "asdas" group by field2

I may have millions of documents. So I want to find most efficient way for querying if

  1. I care about disk capacity and don’t want to use too much resource but use indexes
  2. I don’t care about resources too much and speed is my priority

Can any one help?

IndexAdvisor in the web console → Query tab can help.

There’s also a great section in the documentation.