インデックスを活かせないSQL文の書き方

インデックスを設定した列のデータを対象に関数を適用した場合

mysql> EXPLAIN SELECT id,ndate FROM t_hanbai WHERE DATE_FORMAT(ndate,'%Y/m')='20
0507';
+----------+-------+---------------+-------+---------+------+------+------------
--------------+
| table    | type  | possible_keys | key   | key_len | ref  | rows | Extra
              |
+----------+-------+---------------+-------+---------+------+------+------------
--------------+
| t_hanbai | index | NULL          | ndate |       3 | NULL |    9 | Using where
; Using index |
+----------+-------+---------------+-------+---------+------+------+------------
--------------+
1 row in set (0.02 sec)

DATE_FORMAT関数が返す値をすべて比較をしているから

以下のように指定しないとインデックスを活用できない。

mysql> EXPLAIN SELECT id,ndate FROM t_hanbai WHERE ndate>='2005-07-01' AND ndate
<'2005-08-01';
+----------+-------+---------------+-------+---------+------+------+------------
--------------+
| table    | type  | possible_keys | key   | key_len | ref  | rows | Extra
              |
+----------+-------+---------------+-------+---------+------+------+------------
--------------+
| t_hanbai | range | ndate         | ndate |       3 | NULL |    4 | Using where
; Using index |
+----------+-------+---------------+-------+---------+------+------+------------
--------------+
1 row in set (0.00 sec)

暗黙のデータ型変換が発生する

mysql> EXPLAIN SELECT * FROM t_hanbai WHERE kkbun = 1;
+----------+------+---------------+------+---------+------+------+-------------+
| table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----------+------+---------------+------+---------+------+------+-------------+
| t_hanbai | ALL  | kkbun         | NULL |    NULL | NULL |    9 | Using where |
+----------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.01 sec)

mysql> EXPLAIN SELECT * FROM t_hanbai WHERE kkbun = '1';
+----------+------+---------------+-------+---------+-------+------+-------------+
| table    | type | possible_keys | key   | key_len | ref   | rows | Extra |
+----------+------+---------------+-------+---------+-------+------+-------------+
| t_hanbai | ref  | kkbun         | kkbun |       1 | const |    3 | Using where |
+----------+------+---------------+-------+---------+-------+------+-------------+
1 row in set (0.00 sec)

kkbun列はchar型なので、WHERE kkbun = '1'という具合にシングルクォートを使う必要がある

指定した条件に当てはまらない「not equal」なレコードを探す場合

mysql> EXPLAIN SELECT * FROM t_hanbai WHERE kkbun != '1';
+----------+------+---------------+------+---------+------+------+-------------+

| table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----------+------+---------------+------+---------+------+------+-------------+

| t_hanbai | ALL  | NULL          | NULL |    NULL | NULL |    9 | Using where |

+----------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.01 sec)