http://blog.darkhax.com/2010/05/14/find-queries-missing-indexes-in-your-rails-application

explain.sql

mysql> EXPLAIN SELECT * FROM tf_users WHERE user_id = 'darkhelmet';
+----+-------------+----------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table    | type   | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+----------+--------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | tf_users | system | NULL          | NULL | NULL    | NULL |    1 |       |
+----+-------------+----------+--------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

find_bad_queries.rb

require ENV_PATH

class String
  # http://www.devarticles.com/c/a/Development-Cycles/Tame-the-Beast-by-Matching-Similar-Strings/3/
  module Soundex
    Codes = {
      'b' => 1,
      'f' => 1,
      'p' => 1,
      'v' => 1,
      'c' => 2,
      'g' => 2,
      'j' => 2,
      'k' => 2,
      'q' => 2,
      's' => 2,
      'x' => 2,
      'z' => 2,
      'd' => 3,
      't' => 3,
      'l' => 4,
      'm' => 5,
      'n' => 5,
      'r' => 6
    }
  end

  def phonetic_code
    chars = self.split(//)
    initial = chars.shift.upcase
    chars.map { |c| Soundex::Codes[c.downcase] }.flatten.unshift(initial).to_s
  end

  def remove_color_codes
    self.gsub(/\x1B\[([0-9]{1,2}(;[0-9]{1,2})?)?[m|K]/, '')
  end
end

@codes = {}

File.open(ActiveRecord::Base.logger.instance_eval { @logdev }.filename, 'r') do |f|
  f.each_line do |line|
    if line.match(/ SELECT /)
      l = 'SELECT' + line.remove_color_codes.split('SELECT').last
      @codes[l.phonetic_code] = l
    end
  end
end

bad_queries = @codes.values.select do |query|
  ActiveRecord::Base.connection.select_all("EXPLAIN #{query}").any? do |result|
    result['key'].nil?
  end
end

puts bad_queries

show_lack_of_indexes.sql

mysql> SHOW INDEX FROM tf_users;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tf_users |          0 | PRIMARY  |            1 | uid         | A         |           1 |     NULL | NULL   |      | BTREE      |         |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

with_index.sql

mysql> CREATE INDEX by_user_id ON tf_users (user_id);
Query OK, 1 row affected (0.17 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM tf_users;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tf_users |          0 | PRIMARY    |            1 | uid         | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| tf_users |          1 | by_user_id |            1 | user_id     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM tf_users WHERE user_id = 'darkhelmet';
+----+-------------+----------+------+---------------+------------+---------+-------+------+-------------+
| id | select_type | table    | type | possible_keys | key        | key_len | ref   | rows | Extra       |
+----+-------------+----------+------+---------------+------------+---------+-------+------+-------------+
|  1 | SIMPLE      | tf_users | ref  | by_user_id    | by_user_id | 34      | const |    1 | Using where |
+----+-------------+----------+------+---------------+------------+---------+-------+------+-------------+
1 row in set (0.00 sec)