Essential Rails Plugins for your Inner DBA
Published on December 13, 2009 by Jesse Storimer
I like to think that I’m not bad at writing SQL. When it comes to writing complex queries, joinging tables, grouping columns, sorting, I’m quite comfortable.
However I never seem to give any thought to the possible size of the tables I am working with. Are there hundreds of records? Hundreds of thousands? Millions? This is usually not an issue for local development, however once your app is out in the wild, this is a big issue.
Recently, I have been using two awesome Rails plugins to remind me to not forget these things.
This plugin is great. Basically it adds a js widget to all your pages that runs the EXPLAIN command on each of your queries and lets you know if any warnings were generated.
Just install the plugin and it will be working by default in your development environment. For each problem query, the Query reviewer widget will tell you what the problem is, ie. ‘No index was used. Performed full table scan’, ‘A temporary table had to be used’, etc.
Here is an example where you need to fix something, adding an index in this case.
Here’s where you hope to get to:
This library detects N+1 queries and unused eager loading. Again, something that you probably wouldn’t notice in a development environment but can become very important in a production environment.
For me this is even more important because I am beginning to understand SQL indexes and where they should be used, but eager loading still escapes me at most junctions.
For Bullet, you need to add some config code to your development environment. By default it logs problems to the Rails log, as well as giving you a js alert. You can even get this thing working with Growl, if you’re into that kind of thing.
Here is an example of its output, its pretty smart with its suggestions:
These plugins are a great way to catch database optimizations that you might have missed. However, these suggestions should be taken with a grain of salt. Much like code metrics, they can only go so far. You know your app better than these tools and in some cases it may be the right decision to ignore them.