【MySQL】CONSTRAINTを介さないと、インデックスが貼られない現象について

前提

WordPressでCREATE TABLEしています。

外部キーの設定にあたって、CONSTRAINTを介すとインデックスが自動で貼られ、介さないと貼られないという現象を見つけました。

これにつきまして以下の点を知りたいと思っています。

###知りたいこと
【１】
なぜCONSTRAINTを介さないとインデックスが貼られないのでしょうか？

MySQLでは外部キーを設定すると自動でインデックスが貼られる機能があるはずですが、以下のCREATE文の
FOREIGN KEY (first_ID) REFERENCES wp_firsts(ID),
という行での外部キー設定ではいかにしてその機能を逃れ、インデックスが貼られずに済んでいるのでしょうか？

MariaDBだから自動で貼られないのでしょうか？しかしCONSTRAINTを介した
CONSTRAINT fk_wp_seconds_01 FOREIGN KEY (first_ID) REFERENCES wp_firsts(ID)
という行では自動で貼られていますし、謎です。

【２】
使い道のないインデックスは貼りたくないのですが、外部キーには名前をつけたいです。しかしCONSTRAINTを介さないと名前をつけることはできないと思うのですが、介せばインデックスが自動で貼られてしまいます。このジレンマを解消し、外部キーに名前をつけつつ、インデックスを貼らない方法はあるでしょうか？

【３】
WordPerssユーザーのみなさんは、どのように外部キーをセットしていますか？

以上の３つですが、どれか１つでも幸いです。
ご回答宜しくお願い申し上げます。

###発生している問題
該当のソースコードを実行しますと、wp_secondsテーブルに作成されるインデックスは下図の３つとなります。

CONSTRAINTを介して作成された外部キー（fk_wp_seconds_01）はインデックスが貼られ、介さない方の外部キーでは貼られないのです。

該当のソースコード

wp_firstsテーブルと、そのIDカラムを外部キーに持つwp_secondsテーブルのCREATE文になります。

PHP
1create_wp_firsts();
2create_wp_seconds();
3
4function create_wp_firsts() {
5    global $wpdb;
6	$sql = "CREATE TABLE IF NOT EXISTS wp_firsts (
7		
8		-- カラムを設定
9		ID BIGINT(20) UNSIGNED NOT NULL,
10		title VARCHAR(10) NOT NULL,
11
12		-- 主キーを設定
13		PRIMARY KEY (ID)
14
15	);";
16	add_option($table."_version", '1.0');
17	$wpdb->query($sql);
18}
19
20function create_wp_seconds() {
21    global $wpdb;
22	$sql = "CREATE TABLE IF NOT EXISTS wp_seconds (
23		
24		-- カラムを設定
25		ID BIGINT(20) UNSIGNED NOT NULL,
26		first_ID BIGINT(20) UNSIGNED NOT NULL ,
27		sentence VARCHAR(100) NOT NULL,
28		
29		-- 主キーを設定
30		PRIMARY KEY (ID),
31		
32		-- インデックスを設定
33		INDEX idx_wp_seconds_01(sentence),
34		
35		-- 外部キーを設定
36		FOREIGN KEY (first_ID) REFERENCES wp_firsts(ID),
37		CONSTRAINT fk_wp_seconds_01 FOREIGN KEY (first_ID) REFERENCES wp_firsts(ID)
38
39	);";
40	add_option($table."_version", '1.0');
41	$wpdb->query($sql);
42}

試したこと

wp_secondsテーブルに対しSHOW CREATE TABLEを実行すると以下の出力を得ましたが、さらに訳の分からないことになりました。

SQL
1CREATE TABLE `wp_seconds` (
2 `ID` bigint(20) unsigned NOT NULL,
3 `first_ID` bigint(20) unsigned NOT NULL,
4 `sentence` varchar(100) NOT NULL,
5 PRIMARY KEY (`ID`),
6 KEY `idx_wp_seconds_01` (`sentence`),
7 KEY `fk_wp_seconds_01` (`first_ID`),
8 CONSTRAINT `fk_wp_seconds_01` FOREIGN KEY (`first_ID`) REFERENCES `wp_firsts` (`ID`),
9 CONSTRAINT `wp_seconds_ibfk_1` FOREIGN KEY (`first_ID`) REFERENCES `wp_firsts` (`ID`)
10) ENGINE=InnoDB DEFAULT CHARSET=utf8

この結果によれば、外部キーの設定はいずれもCONSTRAINTを介してされているのです。

ならばなぜ、fk_wp_seconds_01だけインデックスが貼られ、wp_seconds_ibfk_1は貼られないのか…訳がわからなくなてきました。

補足情報（FW/ツールのバージョンなど）

サーバーはGMOサーバーです。
データベースはMariaDBの10.1.11です。
PHPは7.2です。

行動規範の内容に同意します

回答1件

ベストアンサー

###【１】について

「> CONSTRAINTを介さないと、インデックスが貼られない」
ではなく、

「CONSTRAINTを介さなくてもインデックスは貼られるが、しかし同じインデックスは上書きされるために貼られないのだと誤解されてしまった」
です。

改めてcreate_wp_secondsの下記コードを見てみましょう。

        -- 外部キーを設定
        FOREIGN KEY (first_ID) REFERENCES wp_firsts(ID),
        CONSTRAINT fk_wp_seconds_01 FOREIGN KEY (first_ID) REFERENCES wp_firsts(ID)

このコードによって後者のCONSTRAINTを介した方のインデックスで上書きされ、だから図のようにfk_wp_seconds_01というインデックスだけが残っているのです。

前者のCONSTRAINTを介さない方は「> インデックスが貼られずに済んでいる」というわけではなく、後者で上書きされただけということですね。

###【２】について

できません。
仮に外部キーを登録したのちにDROP INDEXを試みてもエラーになるでしょう。
MySQLもMariaDBも、外部キーは常にインデックスとセットです。

###【３】について
個人的には名前をつけられる点に鑑みてCONSTRAINTの方法を採用しています。

投稿2020/06/24 01:23