Tuning Mongo queries with dbKoda
We’ve written about using Mongodb explain and effective MongoDB indexing in the past on the dbKoda blog. In this little post, I want to show you how these principles are supported within the dbKoda product.
Getting a query explain plan
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:
The index advisor
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 again 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.
The Shameless plug
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 totally free, open source (AGPL) product made by a bunch of hipster code monkeys in Melbourne, Australia. As well as query builders and tuning utilities, it supports storage diagnostics, a SQL interface, data and administration utilities, code translators and much more. Check it out and let us know what you think!
Originally published at gist.github.com.
