Getting Started With MongoDB explain()

DZone 's Guide to

Getting Started With MongoDB explain()

For almost all operations, there will be more than one way for MongoDB to retrieve the documents required. When MongoDB parses a statement, it must decide wh...

· Database Zone ·
Free Resource

For almost all SQL statements, there will be more than one way for Oracle to retrieve the rows required. When Oracle parses an SQL statement, it must decide which approach will be fastest. The process of determining this “optimal” path to the data is referred to as query optimization.

Basics of Query Optimization

For instance, consider the following query:

db.mycustomers. find( { FirstName: "RUTH", LastName: "MARTINEZ", Phone: 496523103 }, { Address: 1, dob: 1 } ). sort({ dob: 1 });

There exist indexes on FirstNameLastNamePhone, and dob. This gives MongoDB the following choices for resolving the query:

  1. Scan the entire collection looking for rows that match all the condition, then sort those row by dob.
  2. Use the index on FirstName to find all the “RUTH”s, then filter out those rows based on LastName and Phone, then sort the remainder on dob.
  3. Use the index on LastName to find all the “MARTINEZ”s, then filter out those rows based on FirstName and Phone, then sort the remainder on dob.
  4. Use the index on Phone to find all documents with a matching phone number. Then eliminate any who are not RUTH MARTINEZ, then sort by dob.
  5. Use the index on dob to sort the documents in dob order, then eliminate any documents that don’t match the query criteria.

The query optimizer decides which of these approaches to take. The explain()method reveals the query optimizer’s decision and — in some cases — lets you examine its reasoning.

Getting Started With Explain()

To see the explain output, we use the explain() method of the collection object and pass a find(),update(), insert(), drop(), or aggregate() to that method. For instance, to explain the query above, we could do this:

var explainDoc=db.mycustomers.explain(). find( { FirstName: "RUTH", LastName: "MARTINEZ", Phone: 496523103 }, { Address: 1, dob: 1 } ).

The resulting explainDoc object is a cursor that returns a complex JSON document. The bit that is most important initially is the winningPlan section, which we can extract like this:

mongo> var explainJson = explainDoc.next(); mongo> printjson(explainJson.queryPlanner.winningPlan); { "stage" : "PROJECTION", "transformBy" : { "Address" : 1, "dob" : 1 }, "inputStage" : { "stage" : "SORT", "sortPattern" : { "dob" : 1 }, "inputStage" : { "stage" : "SORT_KEY_GENERATOR", "inputStage" : { "stage" : "FETCH", "filter" : { "$and" : [ { "FirstName" : { "$eq" : "RUTH" } }, { "LastName" : { "$eq" : "MARTINEZ" } } ] }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "Phone" : 1 }, "indexName" : "Phone_1", // I removed some stuff here ] } } } } } }

It’s still pretty complex — and I removed some stuff to simplify it. However, you see it contains multiple stages of query execution which the input to each stage nested as inputStage. So, you sort of read from the inside-out to get the plan. If you want, you can use this snippet to print out the steps in order (this works for most plans, but not for index-merges or for sharded clusters:

mongo> var stepNo=1; mongo> function quick_explain(explainPlan) { var stepNo = 1; var printInputStage = function(step) { if ("inputStage" in step) { printInputStage(step.inputStage); } if ("inputStages" in step) { step.inputStages.forEach(function(inputStage){ printInputStage(inputStage); }); } if ("indexName" in step) { print(stepNo++, step.stage, step.indexName); } else { print(stepNo++, step.stage); } }; printInputStage(explainPlan); } mongo> quick_explain(explainJson.queryPlanner.winningPlan); 1 IXSCAN Phone_1 2 FETCH 3 SORT_KEY_GENERATOR 4 SORT 5 PROJECTION

This provides the execution plan in a very succinct format. Here’s an explanation of each step:

Step Explanation
IXSCAN Phone_1
Used the index to find documents with a matching value for Phone
Filtered out documents based on the Firstname and Lastname criteria
SORT_KEY_GENERATOR Emitted dob attributes for the next step
sorted documents on the dob
Pushed address and dob into the output stream

And that's it!

code optimization ,hairline optimizer ,on-page optimization ,index optimization ,optimizing the organization of a website

Published at DZone with permission of Guy Harrison , DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}