MongoDB not using my index(arrays, boolean)

Question

I have collection full of the following documents, for example:

{       
       "_id" : ObjectId("2819738917238dgd21873"),
       "mailIntid" : 10000000,                                                                                                                                                                                                                                        
       "mailCreated" : "2019-02-08",                                                                                                                                       
       "mailLastModified" : null,                                                                                                                                                                                                                                             
       "mailReceived" : "2019-02-08",                                                                                                                                      
       "mailSend" : "2019-02-08",                                                                                                                                          
       "hadAttachment" : false,                                                                                                                                                                                                                                
       "subject" : "nieuwe vacature ",                                                                   
       "bodyPreview" : "Test Body",
       "importance" : "normal",                                                                                                                                                                                                        
       "isDeliveryRequested" : null,                                                                                                                                       
       "isReadReceiptRequested" : false,                                                                                                                                   
       "isRead" : false,                                                                                                                                                   
       "isDraft" : false,                                                                                                                                                                                                                                                                                                       
       "inferenceClassification" : "focused",                                                                                   
       "bodyContentType" : "html",                                                                                                                                         
       "senderName" : "Jobs",                                                                                                
       "senderEmail" : "[email protected]",                                                                                                                    
       "fromName" : "Jobs",                                                                                                                                          
       "fromEmail" : "[email protected]",                                                                                                                      
       "flagStatus" : "notFlagged",                                                                                                                                        
       "urls" : "https://test.nl/request-details?id=1337",                                                                                                          
       "insertDate" : "2019-02-09",                                                                                                                                        
       "modifiedDate" : "2019-05-05",                                                                                                                                      
       "parseComplete" : true,                                                                                                                                             
       "rawMailUrl" : [                                                                                                                                                    
               {                                                                                                                                                           
                       "url" : "https://test.nl/request-details?id=1337",                                                                                           
                       "parsed" : false                                                                                                                                    
               }                                                                                                                                                           
       ]                                                                                                                                                                   
}

I created a index with the following code:

db.testAB.createIndex(
    {"rawMailUrl.parsed": 1},
    {partialFilterExpression: { "rawMailUrl.parsed": false}}
)

But whenever I use the following query, it's not using the index from above.

db.testAB.find({"rawMailUrl.parsed": false})

Any ideas? Am I doing something wrong with creating a index with arrays and a true or false expression?

Answer 1

You defined an index for partialFilterExpression: { "rawMailUrl.parsed": false}, i.e. all records in your index have the same value false.

An index with low number of distinct values is a bad index, it does not improve the access path. Thus the index is not used, it is just a waste of disc space.