The dbKoda team is very excited and somewhat relieved to announce the latest release of dbKoda 0.9.0. As with all releases of dbKoda, this version is 100% open source, free for use in any circumstances and totally cruelty-free.

In this release we fixed some bugs, created some bugs but tried hard to do more of the former than the later. In addition, we added some significant new features:

Index advisor

dbKoda can advise you on indexes that should be created to avoid COLLSCAN and SORT conditions. This functionality is accessed from the explain plan panel. If you have a query that you want to optimize, start by generating an explain plan using the ? toolbar button:

Query

In this case, the explain generates show both COLLSCAN and SORT steps:

Explain

Clicking the Index Advisor button generates suggested indexes. Because this query contains an $or condition we suggest two indexes. Note also we suggest dropping an existing index. This is because the index on {Rating:1} is now superseded by the proposed index on {Rating:1, Length:-1}.

Advisor

Clicking the Add Code Snippet button moves the suggested index creation statements to the code editor where you can run them. Once we create these indexes we can re-execute the explain to see the effect. As we hope, the COLLSCAN and SORT steps are no replaced by index-based access paths.

NewPlan

Sometimes a COLLSCAN might actually be the most effective choice and MongoDB might decline to use the new index. The breakeven points for index scans vs collection scans were discussed in this blog post. However, in general MongoDB is strongly inclined towards indexes and most of the time it will use indexes in preference to a collection scan. Therefore, you should always benchmark a new index to make sure it is a valid improvement.

Remove unnecessary indexes

When we add indexes in the index advisor we let you know if any existing indexes are rendered unnecessary by the new indexes. You can now also check all existing indexes and remove any that are redundant. For instance if there is an index in {a:1,b:1} and another on {a:1}, we will suggest dropping the index on {a:1}.

You invoke this functionality from the database tree action Drop Unnecessary Indexes:

DropIndexes1

We’ll generate a list of dropIndex statements that you can execute:

DropIndexes1

Update/Insert/Delete support

You can create insert, update or delete statements from a fill-in the blanks form in the database tree. This works in a similar way to the simple query builder. You fill in a form and we generate the appropriate MongoDB commands in the editor. For instance, here we are generating an update statement:

update

Remote SSH terminal

You can do most MongoDB administration tasks from the mongo shell, but sometimes there’s no alternative to logging onto the remote host to perform operating system tasks. We now provide an SSH terminal that connects to the mongoDB host allowing you to start or stop mongo instances, examine OS level configuration, accidentally remove the operating system, and so on.

To use the SSH terminal, you need to specify SSH credentials as shown here:

SSHSetup

When the connection is established an SSH tab will be opened in the output panel:

SSH2

You can execute pretty much any OS command here. For instance you could execute top to see operating system statistics and top processes:

top

SSH is supported natively on Linux and MacOS. Windows 10 provides an SSH server as a optional package and it’s expected that future version of Windows will support SSH natively. For information on using SSH on windows check out this FAQ entry.

You can also open a terminal window to your local desktop machine. This might be handy if you want to run mongo command line tools, invoke a local editor, etc.

Support for Drill SQL (experimental)

We now allow you to issue SQL commands against your MongoDB host using Apache Drill. This feature is a bit “experimental” - it is dependent both on the Apache Drill binaries and requires Java be installed on your local host.

To use this feature, select Query Database with Apache Drill from a database node:

drill1

If this is the first time you’ve invoked Drill, there’ll be a substantial delay while the Apache Drill binary download. You’ll also have to have Java installed before Drill can run. Once Drill is downloaded, you’ll be able to open a Drill SQL editor windows and issue SQL commands against the MongoDB database:

drill2

See also This blog post for some details on how Drill SQL works on embedded MongoDB arrays.

Give it a try!

We think these new features add to what is already a pretty broad set of useful (and free :-) ) dbKoda features. If you’re not aware, here’s a few of the features we’ve introduced in the last couple of releases:

We’d love to know what you think of dbKoda, download it at dbKoda.com, and let us know what you think at Facebook, Twitter, Github or our support channel.