인덱스 존재 유무에 따라서 인덱스를 삭제 하거나 생성하거나 할 때 사용하려고 테스트 해 봄
# Creation Test Table
MariaDB [test]> create table test ( id int , name varchar(10) , part varchar(10) );
# Creation Indexes
MariaDB [test]> create index test_id_idx on test(id) ;
MariaDB [test]> create index test_id_part on test(id , part ) ;
# Creating Index if not exists
MariaDB [test]> create index if not exists test_id_part on test(id,part) ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 1
MariaDB [test]> show indexes from test ;
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test | 1 | test_id_idx | 1 | id | A | 0 | NULL | NULL | YES | BTREE | | |
| test | 1 | test_id_part | 1 | id | A | 0 | NULL | NULL | YES | BTREE | | |
| test | 1 | test_id_part | 2 | part | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
MariaDB [test]> create index if not exists test_id_name on test(id,name) ;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test]> show indexes from test ;
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test | 1 | test_id_idx | 1 | id | A | 0 | NULL | NULL | YES | BTREE | | |
| test | 1 | test_id_part | 1 | id | A | 0 | NULL | NULL | YES | BTREE | | |
| test | 1 | test_id_part | 2 | part | A | 0 | NULL | NULL | YES | BTREE | | |
| test | 1 | test_id_name | 1 | id | A | 0 | NULL | NULL | YES | BTREE | | |
| test | 1 | test_id_name | 2 | name | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)
MariaDB [test]> create index if not exists test_id_idx on test(id,name,part) ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 1
MariaDB [test]> show indexes from test ;
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test | 1 | test_id_idx | 1 | id | A | 0 | NULL | NULL | YES | BTREE | | |
| test | 1 | test_id_part | 1 | id | A | 0 | NULL | NULL | YES | BTREE | | |
| test | 1 | test_id_part | 2 | part | A | 0 | NULL | NULL | YES | BTREE | | |
| test | 1 | test_id_name | 1 | id | A | 0 | NULL | NULL | YES | BTREE | | |
| test | 1 | test_id_name | 2 | name | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)
MariaDB [test]> drop index if exists test_id_name on test.test ;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test]> show indexes from test.test ;
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test | 1 | test_id_idx | 1 | id | A | 0 | NULL | NULL | YES | BTREE | | |
| test | 1 | test_id_part | 1 | id | A | 0 | NULL | NULL | YES | BTREE | | |
| test | 1 | test_id_part | 2 | part | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)