I am building a query engine whose underlying database is DocumentDB (aka MongoDB 3.6) and I was wondering what the best way to build indices for it in order to get the best query performance possible. While there will definitely be certain queries that will be more common than others, the fact that users will be building these queries, the goal is to have good (enough) performance across any combination of attributes to query by.
A document in this collection would have a structure similar to this:
{ "ContainerName" : "hello",
"description" : "test",
"timestamp" : 1000000,
"isActive" : true,
(15 more attributes of strings, booleans, numbers),
"events": [
{ "eventId" : "test",
(10 more attributes of strings, booleans, numbers)
},
{ "eventId" : "test2",
(10 more attributes of strings, booleans, numbers)
}],
"resources": [
{ "resourceId" : "test",
(8 more attributes of strings, booleans, numbers)
}]
}
I want to be able to query all combinations of attributes including the embedded ones. For instance, get me the container whose name is hello and has an event with an eventId of the test. If the number of attributes was small enough, maybe I could have done all possible combinations as a compound key but it wouldn't be possible here.
In addition, if I wanted to use regex for string contains on some fields, would MongoDB use indices on the attributes of the filter it can and then manually filter to complete the regex or does using regex eliminate the use of indices completely for the query.