SQL のWHERE句の条件に関数を使うとインデックスが使用されない

mysql Rails

Rails でfind_by_sql を使う場合に、大きいSQL だと次のようにSQL 中に関数を使ってしまうことがありました。
単純な例だとこんな感じ。

User.find_by_sql([<<-SQL, {:id => 1}])
SELECT *
FROM users
WHERE
  IF(
      :id,
      id = :id,
      1
    )
SQL


でも、このようにIF 関数中に条件式を書いてしまうと、インデックスが使用されません。

[test]> explain SELECT * FROM users WHERE  IF(1, id = 1,  1);
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where | 
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)


「CONCAT」や「*」のようにカラムの値に対して演算するようなSQL に対してインデックスが使われないのは知っていたのですが、IF やIFNULL もダメなんですね。
IF を使用する場合でも、定数側にIF を使う場合はインデックスが使用されます。これはCONCAT とかと同じですね。

[test]> explain SELECT * FROM users WHERE id = IF(1, 1, 2);
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | users | const | PRIMARY       | PRIMARY | 4       | const |    1 |       | 
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)


しかし、この例のように、カラムに対する値(:id)が指定されていない時は条件式毎無視したいという場合は、定数側だけにIF を使用することはできません。
このような場合にはERB を使うとうまくやることができます。

require 'erb'
conditions = {:id => 1}

sql = <<-SQL
SELECT *
FROM users
WHERE
<% if conditions[:id] %>
  id = :id AND
<% else %>
-- :id
<% end %>

1
SQL

User.find_by_sql([ERB.new(sql).result(binding), conditions])
参考