PK以外の通常INDEXのレビュー

————————————————————————
インデックス作成時のインデックス名
————————————————————————

ALTER TABLE … ADD INDEX
インデックス名を指定しないで実行すると, MySQLがインデックスの最初のColumを利用して自動的に名前を付ける。

CREATE INDEX
インデックス名を指定しないで作成しようとするとエラーになる。


mysql> desc test_numbers;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| number | tinyint(3) unsigned | YES | | NULL | |
| string | char(5) | YES | | NULL | |
| dates | date | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> explain select * from test_numbers where string='yahoo';
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test_numbers | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> alter table test_numbers add index idx_tn_string(string);
Query OK, 10 rows affected (0.06 sec)
Records: 10 Duplicates: 0 Warnings: 0

add_index_0

インデックス追加後の実行プラン
add_index_1


mysql> drop index idx_tn_string on test_numbers;
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql> desc test_numbers;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| number | tinyint(3) unsigned | YES | | NULL | |
| string | char(5) | YES | | NULL | |
| dates | date | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> create index idx_tn_string on test_numbers(string);
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql> desc test_numbers;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| number | tinyint(3) unsigned | YES | | NULL | |
| string | char(5) | YES | MUL | NULL | |
| dates | date | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

add_index_2

alter tableにてインデックスのDrop


mysql> desc test_numbers;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| number | tinyint(3) unsigned | YES | | NULL | |
| string | char(5) | YES | MUL | NULL | |
| dates | date | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table test_numbers drop index idx_tn_string;
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql> desc test_numbers;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| number | tinyint(3) unsigned | YES | | NULL | |
| string | char(5) | YES | | NULL | |
| dates | date | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

補足: テーブル作成時にインデックスを追加する例


mysql> CREATE TABLE T_WITH_INDX (
-> col1 INT UNSIGNED NOT NULL,
-> col2 CHAR(50) NOT NULL,
-> col3 CHAR(50) NOT NULL,
-> PRIMARY KEY(col1),
-> UNIQUE(col2),
-> INDEX(col3)
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc T_WITH_INDX;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| col1 | int(10) unsigned | NO | PRI | NULL | |
| col2 | char(50) | NO | UNI | NULL | |
| col3 | char(50) | NO | MUL | NULL | |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

index_01

同じ内容で以下のようにインデックスを作成する事も可能


mysql> CREATE TABLE T_WITH_INDX(
-> col1 INT UNSIGNED NOT NULL PRIMARY KEY,
-> col2 CHAR(50) NOT NULL UNIQUE,
-> col3 CHAR(50) NOT NULL,
-> INDEX(col3)
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc T_WITH_INDX;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| col1 | int(10) unsigned | NO | PRI | NULL | |
| col2 | char(50) | NO | UNI | NULL | |
| col3 | char(50) | NO | MUL | NULL | |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql>

DROP INDEXではまとめて複数のインデックスをDROPする事は出来ません。
纏めて削除したい場合は、ALTER TABLE … DROP INDEXを利用する。

Comments are closed.

Post Navigation