Query report

log_sql.rb

# initializer, run dev or test with LOG_SQL=true to log SQL
connection = ActiveRecord::Base.connection
class << connection
  alias :original_exec :execute
  def execute(sql, *name)
    # try to log sql command but ignore any errors that occur in this block
    # we log before executing, in case the execution raises an error
    begin
        file = File.open(RAILS_ROOT + "/log/queries.sql",'a'){|f| f.puts sql}
    rescue Exception => e
      ;
    end
    # execute original statement
    original_exec(sql, *name)
  end
end if !Rails.env.production? and ENV['LOG_SQL']

query.rake

namespace :query do
  desc "Export data"
  task :indexes => :environment do
    queries = []

    File.open("#{Rails.root}/log/queries.sql") do |f|
      f.each_line do |line|
        queries << line
      end
    end

    queries_with_commit = []
    commit_index = 0
    is_commit = false
    queries.each do |query|
      if query.match(/COMMIT|ROLLBACK/)
        is_commit = false
        commit_index += 1
      end

      is_commit = true if query.match(/BEGIN/)
      if is_commit
        if queries_with_commit[commit_index].nil?
          queries_with_commit[commit_index] = {:count => 1, :queries => [query]}
        else
          queries_with_commit[commit_index][:count] += 1
          queries_with_commit[commit_index][:queries] << query
        end
      end
    end

    puts "Huge transaction"
    queries_with_commit.compact.each do |transaction|
      next if transaction[:count] < 2
      puts "Transaction query count #{transaction[:count]}"
      transaction[:queries].each do |query|
        puts query
      end
    end

    select_query = []
    queries.each do |query|
      if query.match(/SELECT/)
        select_query << query.match(/(SELECT.+)/)[1]
      end
    end

    queries_without_index = select_query.uniq.select do |query|      
      begin
        ActiveRecord::Base.connection.select_all("EXPLAIN #{query}").any? do |result|
          result['key'].nil?
        end
      rescue StandardError => e
        next
      end
    end

    unique_queries_without_index = queries_without_index.map {|query| query.gsub(/'(.+)'/, "'STRING'").gsub(/(\d+)/, "0") }.uniq

    puts "Query without index"
    unique_queries_without_index.each do |query|
      puts query
    end
  end
end