SQL：インデックス

インデックスの作成・確認・削除

# 作成 ： 30秒ほど時間がかかる
$ CREATE INDEX <インデックス名> ON <テーブル名> (カラム名);

$ CREATE INDEX salary_index ON salaries (salary);

mysql> CREATE INDEX salary_index ON salaries (salary);
Query OK, 0 rows affected (32.40 sec)
Records: 0  Duplicates: 0  Warnings: 0
---
# 確認
$ SHOW INDEX FROM <テーブル名>\G
or
$ SHOW INDEX FROM <テーブル名>;

mysql> SHOW INDEX FROM salaries\G
*************************** 1. row ***************************
        Table: salaries
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: emp_no
    Collation: A
  Cardinality: 280229
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: salaries
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 2
  Column_name: from_date
    Collation: A
  Cardinality: 2746427
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 3. row ***************************
        Table: salaries
   Non_unique: 1
     Key_name: salary_index  <- ※ここ！！！
 Seq_in_index: 1
  Column_name: salary
    Collation: A
  Cardinality: 151069
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE  <- ※ここ！！！
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL

---
# 削除
$ DROP INDEX <インデックス名> ON <テーブル名>;

$ DROP INDEX salary_index ON salaries;

mysql> DROP INDEX salary_index ON salaries;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0