I've written about using Mongodb explain and effective MongoDB indexing. In this little post, I want to show you how these principles are supported by our open source MongoDB IDE dbKoda.
dbKoda includes interactive query builders and a visual explain plan. Let's say we have a query that we've built up with the dbKoda Simple Query Builder:
We can obtain an execution plan by clicking on the question mark above the query and selecting the type of explain we would like. Execution Stats is usually the best option. (You might have to highlight the mongoDB query text before hitting the explain button).
Now we get a visual representation of the plan. We talked about how to interpret plans here. This plans tells us that we resolved the query by performing a collection scan, and that it was also neccessary to sort the resulting documents. 120K documents were examined to return just 49 documents - not a particularly satisfactory ratio:
We can get indexing advice for this query by clicking the Index Advisor button above the visual explain:
Now we will see a set of indexing recommendations to support the specific query. In some cases you will also see a list of index delete commands where a new index supersedes an existing index. In this case we are given two recommendations:
If we click the "Add Code" button above the index definitions the index statements will be added to the editor where we can execute them.
Let's look at the execution plan agin now that we have created our indexes. We can see below that we are now merging two index lookups rather than performing a collection scan and that we no longer have a SORT step. Furthermore, the number of documents examined has dropped from 120,000 to just 49 and elapsed time has dropped from 907 ms to less than 1ms.
Knowing how to interpret explain() and the basics of MongoDB indexing is an essential skill for all MongoDB professionals. We've tried to streamline the process in dbKoda to optimize both your time and your application performance. dbKoda is a fully free, open source (AGPL) product made by a bunch of hipster code monkeys in Melbourne, Australia. Check it out and if you want to request new features let us know: our monkeys are eagerly awaiting your feature requests!