How to understand explain output & know what indexes to make?

Working with Data
1

I don’t have any indexes yet & I have this query to do a prefix search of my primary key & sort based on created_at & primary key:

“Slow query”,“attr”:{“type”:“command”,“ns”:“MyProject.task”,“command”:{“find”:“task”,“filter”:
{“_id”:{“$regularExpression”:{“pattern”:“^6de0510e”,“options”:“”}}},“limit”:200,“skip”:0,“sort”:
{“created_at”:-1,“_id”:-1},“lsid”:{“id”:{“$uuid”:“400bc375-e70c-45b8-a50d-fa409ef0bbc8”}},“$db”:“MyProject”},
“planSummary”:“IXSCAN { _id: 1 }“,”keysExamined”:2,“docsExamined”:1,“hasSortStage”:true,“cursorExhausted”:true,“numYields”:0,“nreturned”:1,“queryHash”:“23DD5915”,“planCacheKey”:“7EE4381E”,“reslen”:2049,“locks”:{“ReplicationStateTransition”:{“acquireCount”:{“w”:1}},“Global”:{“acquireCount”:{“r”:1}},“Database”:{“acquireCount”:{“r”:1}},“Collection”:{“acquireCount”:{“r”:1}},“Mutex”:{“acquireCount”:{“r”:1}}},“storage”:{},“protocol”:“op_msg”,“durationMillis”:0}}

I added queries to do prefix search on multiple fields & it changed to COLLSCAN:

"ctx":"conn9","msg":"Slow query","attr":{"type":"command","ns":"MyProject.task","command":{"find":"task","filter":{"current_status":{"$in":["pending_broadcast"]},
"org_id":{"$in":["2323"]},"runsheet":null,"$or":[{"_id":{"$regularExpression":{"pattern":"^mumu","options":""}}},{"tracking_id":{"$regularExpression":{"pattern":"^mumu","options":""}}},
{"from_address.zip_code":{"$regularExpression":{"pattern":"^mumu","options":""}}},{"to_address.zip_code":{"$regularExpression":{"pattern":"^mumu","options":""}}}]},"limit":200,"skip":0,"sort":{"created_at":-1,"_id":-1},"lsid":{"id":{"$uuid":"d6b8b1a2-b5cf-448a-81bd-6352dbaa4c71"}},"$db":"MyProject"},"planSummary":"COLLSCAN","keysExamined":0,"docsExamined":7,"hasSortStage":true,"cursorExhausted":true,"numYields":0,"nreturned":7,"queryHash":"5724515A","planCacheKey":"2468EB7C","reslen":13695,"locks":{"ReplicationStateTransition":{"acquireCount":{"w":1}},"Global":{"acquireCount":{"r":1}},"Database":{"acquireCount":{"r":1}},"Collection":{"acquireCount":{"r":1}},"Mutex":{"acquireCount":{"r":1}}},"storage":{},"protocol":"op_msg","durationMillis":0}}
    {"t":{"$date":"2021-07-13T05:29:27.096+00:00"},"s":"D1", "c":"QUERY",    "id":22790,   "ctx":"conn9","msg":"Received interrupt request for unknown op","attr":{"opId":6223}}

If I’m not wrong, IXSCAN is better than COLLSCAN? What indexes must I add to optimize my query? How can explain output help me in this situation? Thanks

2

Hi @Ariel_Ariel ,

The indexes in mongodb should cover the predicts of a query in order that follow Equality Sort Range order.

The following article explains that:

Please note that the regex operators are not working optimally with indexes and Atlas search (if using Atlas) or text indexes are better for this task.

Your first query is only on _id which has an index by default. I noticed you don’t use objectId in this field but your text. Please note that we do not recommend that and you should use another field (“id”) and index it for this values

In your case I would try to index :

{"current_status" : 1 , "org_id" : 1,"runsheet": 1, "created_at":-1,"_id":-1}

Other fields with regex and range like ^ queries might go to the end but due to the $or which is not selective indexes might not be utelized for those fields.

Best regards,
Pavel

3

thanks for your answer.

Your first query is only on _id which has an index by default. I noticed you don’t use objectId in this field but your text. Please note that we do not recommend that and you should use another field (“id”) and index it for this values

really? i didnt know this. i thought we just supply the _id value (in my case with UUID) if we dont want it to be primitive object ID. Ive used this in all my collections :frowning: lol
what are the consequences of this? why you dont recommend to write the _id?

4

Its not terribly wrong. This field is mainly there for replication purpose in a replica set.

You can obviously write your own but it works best with objectIds…