Comments on: MongoDB: Investigate Queries with explain() and Index Usage (part 2) https://www.percona.com/blog/mongodb-investigate-queries-with-explain-index-usage-part-2/ Tue, 06 Aug 2019 20:00:41 +0000 hourly 1 https://wordpress.org/?v=6.5.2 By: Corrado Pandiani https://www.percona.com/blog/mongodb-investigate-queries-with-explain-index-usage-part-2/#comment-10969670 Fri, 07 Sep 2018 09:20:39 +0000 https://www.percona.com/blog/?p=51938#comment-10969670 Hi Mark. Yes, it should be more clear. It takes some time to familiarize with the JSON output to investigate the execution plans. MySQL is simpler because it provides useful informations in a more concise format. MongoDB provides more details.

As a general rule, you just need to have a look at the “stage” field inside the “winningPlan” subdocuments. If you see somewhere IXSCAN it’s good, because an index is used, if you see COLLSCAN it’s not.

Maybe the presence of more “stage” fields in the is confusing. As you can see in some of the examples you can have more than 1 “stage” field on the “winningPlan” because there is an additional “inputStage” subdocument. The “inputStage” is a document that describes the child stage, which provides the documents or index keys to its parent. The field is present if the parent stage has only one child. In most of the cases the parent “stage” should be “FETCH”, In more complex queries, where you have for example OR conditions or where there is some kind of indexe intersection there could be more “inputStages”.
In case of a COLLSCAN no inputStages are present and it’s very simple to understand what’s going on.

]]>
By: Mark Callaghan https://www.percona.com/blog/mongodb-investigate-queries-with-explain-index-usage-part-2/#comment-10969669 Thu, 06 Sep 2018 17:24:52 +0000 https://www.percona.com/blog/?p=51938#comment-10969669 How do I know whether a plan is index-only? This used to be easier to spot in the explain output.

]]>