I have a collection in MongoDB that has ~2Million Documents. Average document size is 28KB. There are several fields but we are concerned with three fields namely - "time", "test_time" and "arr". "time" is a string field and stores the date and time in string format, "test_time" is a Date field and stores the exact same date and time in Date format. "arr" is an array of objects with ~10 fields in each object. Average length of an "arr" is ~5. We are concerned with only one of those fields called "name" , so basically arr.name The pipeline is as follows - a match stage on the "time" field with greater than and less than some particular dates X and Y. After that a projection field of projecting only {arr.name:1, _id:0} In another scenario I run the same pipeline but instead of "time" we use "test_time". Same projection as above. Q1. I do not see a change in the speed when searching for ranged queries on a STRING field VS a DATE field. I would believe the Date type searching in MongoDB would be the faster one. Could you help me investigate why could this be the case? Q2. I have the following indexes - time_1, test_time_1, time_1_arr.name_1 and test_time_1_arr.name_1 By default mongodb uses the time_1 or the test_time_1 index and not the compound indexes with arr.name. I understand it is not a covered query because in my match condition there is only a match condition on the time field and not the name field. I am not sure how to make it a covered query so that it can run much faster. Currently the docsExamined = keysExamined = nReturned which means it is doing disk reads to examine the docs. Is there some way I could optimise this? To fetch ~35000 documents it takes ~6 seconds which seems a lot given that I am projecting only one field of arr.name and one document size is ~28KB. I also realise that an aggregation pipeline is assigned only 100MB of RAM by default so how can I utilise the RAM of my machine to be fully optimised. Continue reading...
