When we start programming, one thing comes in our mind, which
is performance. There are many factors of performance, but query
optimization/analyzing and database structure are more important.
In this article, we will evaluate the performance of the
query, Analyzing query is an aspect of
measurement of database and indexing effectiveness.
db.collection.explain("executionStats")
methods provide statistics about the performance of the query. This data output can be useful in measuring if and how a
query uses an index.
Using $explain
It provides statistics about the performance of the query.
Evaluate the
performance of query
{ "_id" : 1, "ISBN" : "22345654562349",
type: " Paper", quantity: 500 }
{ "_id" : 2, " ISBN " :
"3345678765678", type: " Paper ", quantity: 100 }
{ "_id" : 3, " ISBN " :
"1232234543551", type: " Paper ", quantity: 200 }
{ "_id" : 4, " ISBN " :
"5545533344555", type: " Paper ", quantity: 150 }
{ "_id" : 5, " ISBN " :
"9988844545457", type: " Paper ", quantity: 300 }
|
Query With No Index
db.Book.find(
{ quantity: { $gte: 100,
$lte: 200 } }
)
|
Query Returns result
{ "_id" : 2, " ISBN " :
"3345678765678", type: " Paper ", quantity: 100 }
{ "_id" : 3, " ISBN " :
"1232234543551", type: " Paper ", quantity: 200 }
{ "_id" : 4, " ISBN " :
"5545533344555", type: " Paper ", quantity: 150 }
|
$explain on the
following query:
db.Book.find(
{ quantity: { $gte: 100,
$lte: 200 } }
).explain("executionStats")
|
The above explain() query returns the following analyzed
result:
{
"queryPlanner" : {
"plannerVersion" : 1,
...
"winningPlan" : {
"stage" :
"COLLSCAN",
...
}
},
"executionStats" :
{
"executionSuccess" : true,
"nReturned" :
3,
"executionTimeMillis" : 0,
"totalKeysExamined" : 0,
"totalDocsExamined"
: 5,
"executionStages" : {
"stage" :
"COLLSCAN",
...
},
...
},
...
}
|
The fields in this result set:
queryPlanner.winningPlan.stage
: COLLSCAN, which indicates a collection is scanned.
executionStats.nReturned : 3 which indicates that the query matches and returns three documents.
executionStats.totalDocsExamined : 5, which indicates that MongoDB had to scan five documents (i.e., all documents in the collection) to find the three matching documents.
Now we indexing field
quantity.
db.book.createIndex( { quantity: 1 } )
|
To view the query
plan statistics, use the explain("executionStats") method:
db.Book.find(
{ quantity: { $gte: 100,
$lte: 200 } }
).explain("executionStats")
|
The explain() method
returns the following results:
{
"queryPlanner" : {
"plannerVersion" : 1,
...
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern"
: {
"quantity" : 1
},
...
}
},
"rejectedPlans" : [ ]
},
"executionStats" :
{
"executionSuccess" : true,
"nReturned"
: 3,
"executionTimeMillis"
: 0,
"totalKeysExamined" : 3,
"totalDocsExamined" : 3,
"executionStages" : {
...
},
...
},
...
}
|
queryPlanner.winningPlan.stage
: IXSCAN which indicates a
the collection is scanned.
No comments:
Post a Comment
Please do not enter any spam link in the comment box.