MongoDB Query Optimization and Profiling

• When you work with MongoDB and if your queries is taking time to run and return resultset, then need to check execution query plan of your query, which will help you know, execution time of query, does query used colscan or indexscan, does any index used with query run etc.
• Based on query plan, you should be able to find the root cause and mostly we have seen, if your query is using "colscan" that means, there is no index for the query being use and that causing slowness of query run.
• So, to avoid colscan, you can create required index on your collection and then recheck your query execution time and query plan.
• Refer below steps, to find query plan and apply index.

##Run below query plan, this will give you what query plan used, or no query plan used, which index used or no index used etc.

##If there is no index on table or index does not used for your query, then query plan stage will have as "COLSCAN" and if index created and used with your query, then it will show "IDXSCAN" along with index name used/applied.

##Other than, stage "COLSCAN", "IDXSCAN", there value could be "EOF" and that means, query plan was wrong like either because of wrong namespace or host etc.

##Below both query are same, so either you run by passing "queryPlanner" or not, but are same to give you query plan.

##Here "Order_Collection" is my collection/table name and "ItemInvoice_Number" is field where I am running where condition to find "T000001" record.

db.Order_Collection.find({"ItemInvoice_Number":"T000001"}).explain()

db.Order_Collection.find({"ItemInvoice_Number":"T000001"}).explain("queryPlanner")

##here below is the screenshot, when there is no index

##Now single column index created on transaction field, then ran same above query:

##To check query execution time with plan or check no. of records examined and no. records returned from total for query then, use "executionStats".

db.Order_Collection.find({"ItemInvoice_Number":"T000001"}).explain("executionStats")

##Below screenshot when there was no index, so examined all records of that collection, so there was only 4 records, so it showing same along with execution time:

##Now same above query after creating single column index and it only examined 1 records having same transaction number value, so in that case definitely execution time will be less:

• Now question comes, how you will identify your expensive queries, so that you could check the execution plan for those query/collections and then you may would like to create index and recheck your query execution time/plan.
• To get expensive query, you need to enable profiling on your MongoDB, so that it will start capturing your all the queries executions depending on profiling level you enabled.
• Refer below section, where how we can enable different level of profiling on MongoDB, check the status of profiling and finally find the expensive queries.

##get profiler status level, is it enabled or disabled and how it enabled

##NOTE - below profiling query you will be only be able to run with root user role, else it will throw unauthorize error.

db.db.getProfilingLevel()

• Level 0 - The profiler is off and does not collect any data. This is the default profiler level.
• Level 1 - The profiler collects data for operations that take longer than the value of slowms(default 100 milli second). So "slowms" field which will take how many milli second any query will run only then it will consider as slow query and will be included into profiling
• Level 2 - The profiler collects data for all operations. In this case, if you even passing "slowms", then that will be ignored and it will capture all queries.

##Get current profiling status, so it will give you level as ("was" field), but along with that "slowms" field which will take how many milli second any query will run only then it will consider as slow query and will be included into profiling.

db.getProfilingStatus()

##To set the profiling level

db.setProfilingLevel(1)

##if you want to pass slowms value, then pass into second parameter

##query syntax means #db.setProfilingLevel(<level>, <slowms - i.e. slow in milli second>)

db.setProfilingLevel(1, 5)

##Once profiler is enabled, then leave your DB for some time and make sure to run all the queries, so that profiler will capture all the queries.

##Run below query to find all the queries, which is taking more than 5 millisecond.

db.system.profile.find( {millis : { $gt : 5 } } ).sort( { ts: 1} );

##Now depending on your above query run resultset, you can try create index and recheck query execution time and query plan and hope this will optimize your query execution time.