After doing some work on optimising a query in Rails app I am working on I got to thinking about how useful MySQL’s explain statement is. It happens to be really useful for figuring out what, if any, indexes are being used by a given select statement. So I decided to see what I could do about adding that functionality to Rails myself. Projects like this really give you a great tour through a lot of Rails internals, so I figured it would be good for me whether I could figure it out or not. After a little hacking I came up with a patch to ActiveRecord::Base.connection that runs an explain statement before every select. I wrapped it in an unless statement to make sure it does not run if the mode is production.
Explain (0.000000) | select_type: SIMPLE | key_len: 263 | table: taggings | id: 1 | possible_keys: index_taggings_on_tag_id,index_taggings_on_taggable_id_and_taggable_type | type: ref | Extra: Using where | rows: 1 | ref: const,const | key: index_taggings_on_taggable_id_and_taggable_type
Tag Load (0.000910) SELECT `tags`.* FROM `tags` INNER JOIN taggings ON tags.id = taggings.tag_id WHERE ((`taggings`.taggable_id = 5) AND (`taggings`.taggable_type = ‘Member’))
The “possible keys” portion is where it lists the indexes for that apply to that query. If there are none there and this is a query that runs a fair bit you probably want to think about adding an index. For me this is a great way to make indexes, or a lack thereof more visible to me during development because they are really easy to forget. So in the spirit of “If you want to truly understand something, try to change it”, here is my first bit of tinkering with ActiveRecord:
unless RAILS_ENV == ‘production’
module ActiveRecord
module ConnectionAdapters
class MysqlAdapter < AbstractAdapter
def select_with_explain(sql, name = nil)
explanation = execute_with_disable_logging(‘EXPLAIN ‘ + sql)
e = explanation.all_hashes.first
exp = e.collect{|k,v| ” | #{k}: #{v} “}.join
log(exp, ‘Explain’)
select_without_explain(sql, name)
end
def execute_with_disable_logging(sql, name = nil) #:nodoc:
#Run a query without logging
@connection.query(sql)
rescue ActiveRecord::StatementInvalid => exception
if exception.message.split(“:”).first =~ /Packets out of order/
raise ActiveRecord::StatementInvalid, “‘Packets out of order’ error was received from the database. Please update your mysql bindings (gem install mysql) and read http://dev.mysql.com/doc/mysql/en/password-hashing.html for more information. If you’re on Windows, use the Instant Rails installer to get the updated mysql bindings.”
else
raise
end
end
alias_method_chain :select, :explain
end
end
end
end
There it is, quick and dirty. If it ends up being something that I, or other people find really useful I will look at cleaning it up, adding some tests and maybe making a gem out of it. In the mean time you can just paste it into a file in your initializers directory. Happy indexing!
Caveats: I have got this working with Rails 2.1 with MySQL. Its only intended for helping out a little during development, and I have no idea what might happen if you run it elsewhere. If you have some suggested improvements I would love to hear them.
