Learning by Doing

This is just to quickly document an alternative way to run explain plan assuming this is the query we want to run explain plan 2019-10-12T06:10:44.366-0400 I COMMAND [conn25251540] command device-event-store.events command: find { find: "events", filter: { $and: [ { aggregateId: { identifier: "990003494014489" } }, { version: { $gte: 0 } } ] }, sort: { version: 1 }, $db: "device-event-store", $clusterTime: { clusterTime: Timestamp(1570875044, 17), signature: { hash: BinData(0, 502D14059FA04824080CA91DAB899D434CFA3E87), keyId: 6727232173650214920 } }, lsid: { id: UUID("91bed447-28f5-4211-b3f8-58821d04060d") } } planSummary: COLLSCAN keysExamined:0 docsExamined:56299 hasSortStage:1 cursorExhausted:1 numYields:441 nreturned:78 reslen:44800 locks:{ Global: { acquireCount: { r: 884 } }, Database: { acquireCount: { r: 442 } }, Collection: { acquireCount: { r: 442 } } } protocol:op_msg 232ms first let's create an explainable object with execut

I am hoping this post will be obsolete and noone will needs to find this. Continue from my previous post about choosing the best index for the job, that certain should still holds true as when I created the index with 1, -1 for two different date field, it's less efficient than -1, -1. Anyway, so luckily I found out about this before rolling this into production as I was planning on the production release. Under lower environment, I realized a less efficient index was used... from my previous post . I now believe it's a bug that should be fixed, in my experiments, the newer index is always preferred even when a better index is present. MongoDB query optimizer looks to be looking into only the first selective field I chose and no further. So if the latest index created matches the field this index is created, so in order for my query to serve the high frequency query, I have to make sure the best index is created last.

This is the query I am working to make it better, Environment: MongoDB 3.6.6 2019-09-25T11:30:17.696-0400 I COMMAND  [conn19014149] command task.task command: find { find: "task", filter: { $and: [ { _id.boardId.location.correlation: "MN01" }, { $or: [ { _id.boardId.boardDate: new Date(1569384000000) }, { _id.boardId.boardDate: new Date(1569297600000), started: { $exists: true }, ended: { $exists: false }, shift.end: { $gte: new Date(1569384000000) } }, { _id.boardId.boardDate: new Date(1569470400000), started: { $exists: true }, ended: { $exists: false }, shift.start: { $lte: new Date(1569470400000) } } ] } ] }, $db: "task", $clusterTime: { clusterTime: Timestamp(1569425411, 1), signature: { hash: BinData(0, A8F704450DF4EF062ABB018B8BC3C175CE3CC62A), keyId: 6680100327825342465 } }, lsid: { id: UUID("683fbed5-1856-4181-b18f-d06db6b6cbb8") } } planSummary: IXSCAN { _id.boardId.boardDate: 1 }, IXSCAN { _id.boardId.boardDate: 1, personnelAssignm