Finding queries that are missing index in MongoDB while development

It's a common mistake that while development developers may write a MongoDB query which may fail to use indexes and does a full table scan. These will become bottle necks in production.

Following is one easy way that I follow to know the bad queries while development stage itself (assuming you are have a similar production dataset in development environment)

1. Find the MongoDB log file path

Locate the MongoDB log file path. In Ubuntu it's typically /var/log/mongo.log. In MacOS you can find at /usr/local/var/log/mongodb/mongo.log

2. Tail on the MongoDB logs

tail -f mongo.log

3. Look for COMMAND info level logs

Mongo will log the slow queries in info level for you, following is a sample log

2020-10-04T18:08:09.315+0530 I  COMMAND  [conn71] command oc.posts 
command: find { find: "posts", filter: { parent: "3w8g8ecv7", visibility: "public" }, sort: { _id: -1 }, 
returnKey: false, 
showRecordId: false, 
lsid: { id: UUID("0092934b-9ae5-22ae-87c8-4dsdsd40ahje1") }, $db: "oc" } 
planSummary: IXSCAN { _id: 1 } 
👉keysExamined:184524 
👉docsExamined:184524 
cursorExhausted:1 
numYields:1441 nreturned:1 queryHash:01609D22 planCacheKey:683AEF07 reslen:435 locks:{ ReplicationStateTransition: { acquireCount: { w: 1442 } }, Global: { acquireCount: { r: 1442 } }, Database: { acquireCount: { r: 1442 } }, Collection: { acquireCount: { r: 1442 } }, Mutex: { acquireCount: { r: 1 } } } storage:{} protocol:op_msg 
👉258ms

If you see the log, I have pointed some of the points we need to look for keysExamined, docsExamined this means to find the data for this query Mongo ended up examining 184524 records so clearly an index is missing here.

You can also take this query and do the explain plan in Mongo Compass to see more details.

So in this case adding an index on parent field will address the issue.