[mysql] index 추가/제거

index 정보 보기

show index from 테이블_이름;

mysql> show index from 20121019_log;              
+--------------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table                    | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| 20121019_log             |          0 | PRIMARY    |            1 | num         | A         |     3005986 |     NULL | NULL   |      | BTREE      | NULL    | 
| 20121019_log             |          1 | ipv4       |            1 | ip          | A         |       39552 |     NULL | NULL   |      | BTREE      | NULL    | 
| 20121019_log             |          1 | timestamp  |            1 | time        | A         |         344 |     NULL | NULL   |      | BTREE      | NULL    | 
+--------------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)

index 제거

alter table 테이블_이름 drop index 인덱스명;

mysql> alter table 20121019_log drop index ipv4; 
Query OK, 3005986 rows affected (17.16 sec)
Records: 3005986  Duplicates: 0  Warnings: 0

mysql> 

index 추가

alter table 테이블_이름 add index 인덱스명 (컬럼명);

mysql> alter table 20121019_log add index ipv4 (ip);