takes a paste of query statements from New Relic and explains all of them ; also converts UPDATEs and DELETEs now

nr_query_analyzer.rb

#!/usr/bin/env ruby
puts "NR Bulk Query Explainer"
puts "make sure to adjust the mysql command line in the script to your local setup"
puts "Paste your SQL Statements Trace from New Relic and ~ and RETURN to submit."
cli = "mysql -uroot notes_from_scratch -e "

strings,queries = [],[]
while (s=gets)!="~\n"
  strings << s
end

# transform the update and delete queries to something explainable
strings.each do |s|
  next if (s.strip)==""
  string_parts = s.split("\t")
  string_parts.each do |p|
    first = (p[0..5] ? p[0..5].downcase : "")
    if first=="delete"
      # DELETE FROM `users` WHERE `id` = ?
      p.gsub!("DELETE FROM ","SELECT * from ")
      (queries << p) and break
    elsif first=="update"
      # UPDATE `subscriptions` SET user_guid = ? WHERE (user_guid = ?)
      p.gsub!("UPDATE ","SELECT * from ")
    
      select,table,rest = p.split("`")
      where_pos = (rest =~ /WHERE/)
      where = rest[where_pos..rest.length] if where_pos
      if where
        query = [select.strip,"`#{table}`",where.strip].join(" ")
        queries << query
      end
      break
    elsif first.downcase=="select"
      (queries << p) and break
    end
  end
end

# fix up the data in the queries
final_queries = []
queries.each do |q|
  candidate = q.strip.gsub("LIMIT ?", "LIMIT #{rand(20) + 1}").gsub("?", "'1'").gsub("`","``")
  final_queries << candidate
end

puts '=='
cnt = 1
final_queries.each do |q|
  eq = "#{cli} \"EXPLAIN #{q}\""
  puts "#{cnt}. #{q}"
  puts "-"
  IO.popen(eq) { |io| puts io.read }
  puts "-"
  2.times { puts }
end
puts "=="