目次
インデックス（INDEX）を作成する
SQLの「CREATE INDEX」または「ALTER TABLE」を使うことで、データベースのテーブルにインデックス（INDEX）を付与することができます。
INDEX作成の基本構文
CREATE INDEX インデックス名 ON テーブル名 ( カラム名1, カラム名2・・・ );
または
ALTER TABLE テーブル名 ADD INDEX インデックス名 ( カラム名1, カラム名2・・・ );
スポンサーリンク
「CREATE INDEX」でインデックスを作成する
■CREATE INDEXの実行例 ※MySQLの例
CREATE INDEX idx_sample ON sample (id, age);■INDEXの確認
> SHOW INDEX FROM sample;
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sample | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| sample | 1 | idx_sample | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| sample | 1 | idx_sample | 2 | age | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)「ALTER TABLE」でインデックスを作成する
■ALTER TABLE ADD INDEXの実行例 ※MySQLの例
ALTER TABLE sample ADD INDEX idx_sample2 (name);■INDEXの確認
> SHOW INDEX FROM sample;
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sample | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| sample | 1 | idx_sample2 | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)インデックス（INDEX）を削除する
SQLの「DROP INDEX」または「ALTER TABLE」を使うことで、テーブルのインデックス（INDEX）を削除することができます。
INDEX削除の基本構文
DROP INDEX インデックス名 ON テーブル名; ※MySQLの場合
DROP INDEX インデックス名 ※Oracle、PostgreSQLの場合
または
ALTER TABLE テーブル名 DROP INDEX インデックス名;
「DROP INDEX」でインデックスを削除する
■INDEX削除前
> SHOW INDEX FROM sample;
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sample | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| sample | 1 | idx_sample2 | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)■DROP INDEXの実行例 ※MySQLの例
DROP INDEX idx_sample2 ON sample;■INDEX削除後
> SHOW INDEX FROM sample;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sample | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.02 sec)「ALTER TABLE」でインデックスを削除する
■INDEX削除前
> SHOW INDEX FROM sample;
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sample | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| sample | 1 | idx_sample | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| sample | 1 | idx_sample | 2 | age | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)■ALTER TABLE DROP INDEXの実行例 ※MySQLの例
ALTER TABLE sample DROP INDEX idx_sample;■INDEX削除後
> SHOW INDEX FROM sample;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sample | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)helpful
この記事は役に立ちましたか？