0

For our app I'm using the free-tier (for now) on MongoDB-Atlas. our documents have, among other fields, a start time which is a Datetime object, and a userId int.

export interface ITimer {
    id?: string,
    _id?: string, // id property assigned by mongo
    userId?: number,
    projectId?: number,
    description?: string,
    tags?: number[],
    isBillable?: boolean,
    isManual?: boolean,
    start?: Date,
    end?: Date,
    createdAt?: Date,
    updatedAt?: Date,
    createdBy?: number,
    updatedBy?: number
};

I'm looking for an index that will match the following query:

let query: FilterQuery<ITimer> = {
    start: {$gte: start, $lte: end},
    userId: userId,
};

Where start and end parameters are date objects or ISOStrings passed to define a range of days.

Here I invoke the query, sorting results:

let result = await collection.find(query).sort({start: 1}).toArray();

It seems simple enough that the following index would match the above query:

{
    key: {
        start: 1,
        userId: 1,
    },
    name: 'find_between_dates_by_user',
    background: true,
    unique: false,
},

But using mongodb-compass to monitor the collection, I see this index is not used. Moreover, mongodb documentation specifically states that if an index matches a query completely, than no documents will have to be examined, and the results will be based on the indexed information alone. unfortunately, for every query I run, I see documents ARE examined, meaning my indexes do not match.

Any suggestions? I feel this should be very simple and straightforward, but maybe I'm missing something. attached is an screenshot from mongodb-compass 'explaining' the query and execution.

linked image

Samuel Philipp
  • 10,631
  • 12
  • 36
  • 56
Maor Barazani
  • 11
  • 2
  • 1
    *"... documentation specifically states that if an index matches a query completely, than no documents will have to be examined..."* - That's a misreading of the documentation. The only case where **documents** ( and not just the **index entries** ) don't need to be "consulted" ( a bit more accurate term ) in order to return result is when you **only return those fields present in the index data itself**. This is called a [covered query](https://docs.mongodb.com/manual/core/query-optimization/#covered-query), and it requires you `project()` **only** those fields present in the compound index. – Neil Lunn May 06 '19 at 10:08
  • As for "linked images", not really happy about that. The content on the screen is "text" when you choose that **RAW JSON** button right next to the one you are on. We would really prefer to see that one. And that means selecting the "text" and including it within your question. Not a screenshot. Also `IXSCAN` means the index **is indeed used**. – Neil Lunn May 06 '19 at 10:09
  • Thanks for the suggestions @NeilLunn, however even after projecting results, using just one of the fields {_id:0 , userId:1}, I have 'documents examined' even though I can tell an index had been used from this query. and to return to the original question, even if I'm not paying any importance to 'documents examined', still I dont see the compound index used. is it misconfigured for this query? _EDIT*: after dropping the single field index 'userId:1', I can see that the compound index 'start:1, userId:1' is used for the same query. and this raises another question about indexing strategies: – Maor Barazani May 06 '19 at 11:29
  • should I index every single field as a single field index, regardless of compound indexes that will cover my queries? – Maor Barazani May 06 '19 at 11:33
  • A compound index should index for equality first, and then by range. So try recreating your index as `{userId: 1, start: 1}`. – JohnnyHK May 06 '19 at 12:45

0 Answers0