EXPLAIN文を使ってインデックスの利用状況を把握

SQL

インデックスの設定にALTER TABLE文を使う。

mysql> EXPLAIN SELECT id,ndate FROM t_hanbai WHERE ndate = '2005-08-11';
+----------+------+---------------+------+---------+------+------+-------------+
| table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----------+------+---------------+------+---------+------+------+-------------+
| t_hanbai | ALL  | NULL          | NULL |    NULL | NULL |   10 | Using where |
+----------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

mysql> ALTER TABLE t_hanbai ADD INDEX ndate(ndate);
Query OK, 9 rows affected (0.21 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT id,ndate FROM t_hanbai WHERE ndate = '2005-08-11';
+----------+------+---------------+-------+---------+-------+------+--------------------------+
| table    | type | possible_keys | key   | key_len | ref   | rows | Extra              |
+----------+------+---------------+-------+---------+-------+------+--------------------------+
| t_hanbai | ref  | ndate         | ndate |       3 | const |    1 | Using where; Using index |
+----------+------+---------------+-------+---------+-------+------+--------------------------+
1 row in set (0.01 sec)