目次
インデックス（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
この記事は役に立ちましたか？