MongoDB does not choose expected index

Working with Data
,
1

Hello.

Using MongoDB 4 or 6 with C# driver.

I’ve a production server that needs to be reboot each Saturday.

The issue appears after this reboot, for a particular query running for time.

The concerned collection has several indexes, which are not overlapping except for one field. Two of these indexes are the following:

This one, which is expected to be used in the query:

         {
                "v" : 2,
                "key" : {
                        "Summary.ActivityId" : 1,
                        "ProductionKeys.Order" : 1
                },
                "name" : "Summary.ActivityId_1_ProductionKeys.Order_1"
        },

And this other one which is effectively choosed by MongoDB:

        {
                "v" : 2,
                "key" : {
                        "Summary.ActivityId" : 1,
                        "ProductionKeys.OrderGroupModelId" : 1
                },
                "name" : "Summary.ActivityId_1_ProductionKeys.OrderGroupModelId_1"
        },

The query is only using the two fields of the first index mentioned above.

But Mongo uses the second one and makes a full scan of the collection (the query result in consequence is obtained after 30mn instead of 3s in normal cases).

Why can be the cause of that, as the query uses exact fields contained in one index?

Note:

All this is included in a ( now old) C# application and does not allow me to easily use “explain”, etc.

2

Hi ,

Can you share the entry in the slowlog for that query ?
if that it’s possible (since it’s prodcution) you could from that slowlog entry then run the query yourself via the mongo shell with explain.

1 Like