Rails SQL Optimization

In order to automate the optimization of SQL queries in Ruby on Rails, I wrote this script to parse the output of the logs. It finds all the unique SQL queries made by different controllers, and runs an SQL EXPLAIN for each. This is really good for finding missing indexes and large table scans.

I have only tested this on Mac/MySQL, so your mileage may vary (it uses *NIX file paths). This script has saved me MUCH time. Of course, when optimizing queries, don't go overboard on eager loading. ActiveRecord eager loading can be slow if you use multiple nested includes.

Just run this script within your Rails application root directory, and it will create a file named explained.html. You may set the RAILS_ENV environment variable to specify production mode. The script automatically loads your database configuration from log/database.yml

Oh, and there needs to be at least 2 pageloads in your log. I haven't bothered fixing this bug.

So here is the script.

UPDATE: I came across some good tips for additional optimization here.