MySQLで関数ベースのインデックスは作成できる？作成方法と確認手順を解説

MySQLでは、バージョン5.6より前の環境では関数ベースのインデックスを作成することができませんでした。本記事では、MySQLでインデックスを作成する具体的な手順を、テーブルの作成からインデックスの確認まで順を追って解説します。

1. サンプルテーブルの作成

まず、インデックスを作成する対象となるテーブルを用意します。以下のクエリを実行して、FirstNameカラムを持つシンプルなテーブルを作成しましょう。

mysql> create table FunctionIndexDemo
    -> (
    -> FirstName varchar(100)
    -> );
Query OK, 0 rows affected (0.70 sec)

Query OKと表示されれば、テーブルの作成は成功です。

2. 関数ベースのインデックスを作成する構文

MySQLでカラムに対してインデックスを作成する場合、基本的な構文は次のとおりです。カラム名の後ろに整数値を指定することで、文字列の先頭部分だけを対象とした「プレフィックスインデックス」を作成できます。

create index index_name on yourTableName (column_name(IntegerSize));

• index_name: 作成するインデックスの名前
• yourTableName: インデックスを作成するテーブル名
• column_name(IntegerSize): 対象カラムと、インデックス化する文字数

それでは、実際にFunctionIndexDemoテーブルのFirstNameカラムに対して、先頭6文字を対象としたインデックスを作成してみましょう。

mysql> create index indFirstName on FunctionIndexDemo (FirstName(6));
Query OK, 0 rows affected (0.56 sec)
Records: 0  Duplicates: 0  Warnings: 0

エラーが表示されずに完了すれば、インデックスの作成は成功しています。

3. 作成したインデックスの確認方法

インデックスが正しく作成されているかどうかは、SHOW INDEXステートメントで確認できます。

mysql> SHOW INDEX FROM FunctionIndexDemo;

実行結果は以下のとおりです。

+-------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table             | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+-------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| functionindexdemo |          1 | indFirstName |            1 | FirstName   | A         |           0 |        6 |   NULL | YES  | BTREE      |         |               | YES     |
+-------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
1 row in set (0.24 sec)

この出力から、以下のことが確認できます。

• Key_name: 作成したインデックス名「indFirstName」が登録されている
• Column_name: 対象カラムは「FirstName」
• Sub_part: 「6」と表示されており、先頭6文字分がインデックス化されている
• Index_type: BTREE方式のインデックスとして作成されている

補足：MySQL 8.0.13以降での真の関数インデックス

上記の例のように、MySQL 5.6以降でもカラムの一部（プレフィックス）に対するインデックスは作成可能です。しかし、UPPER()やLOWER()などの関数の計算結果そのものにインデックスを張る「関数インデックス（functional key part）」は、MySQL 8.0.13以降で正式にサポートされました。

例えば、MySQL 8.0.13以降では次のように記述できます。

mysql> CREATE INDEX idx_upper_name ON FunctionIndexDemo ((UPPER(FirstName)));

これにより、大文字小文字を変換した検索などもインデックスを活用して高速化できるようになります。古いバージョンのMySQLを使用している場合は、生成カラム（GENERATED COLUMN）を作成しそれに対してインデックスを張ることで、同様の効果を実現する方法もあります。