質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

WordPress

WordPressは、PHPで開発されているオープンソースのブログソフトウェアです。データベース管理システムにはMySQLを用いています。フリーのブログソフトウェアの中では最も人気が高く、PHPとHTMLを使って簡単にテンプレートをカスタマイズすることができます。

MariaDB

MariaDBは、MySQL派生のオープンソースなリレーショナルデータベースシステムです。 また、MySQLとほぼ同じデータベースエンジンに対応しています。

Q&A

解決済

1回答

830閲覧

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

mikan123

総合スコア1

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

WordPress

WordPressは、PHPで開発されているオープンソースのブログソフトウェアです。データベース管理システムにはMySQLを用いています。フリーのブログソフトウェアの中では最も人気が高く、PHPとHTMLを使って簡単にテンプレートをカスタマイズすることができます。

MariaDB

MariaDBは、MySQL派生のオープンソースなリレーショナルデータベースシステムです。 また、MySQLとほぼ同じデータベースエンジンに対応しています。

0グッド

0クリップ

投稿2020/06/23 23:07

編集2020/06/23 23:16

前提

WordPressでCREATE TABLEしています。

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

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

###知りたいこと
【1】
なぜ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)
という行では自動で貼られていますし、謎です。

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

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

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

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

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です。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答1

0

ベストアンサー

###【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を介さない方は「> インデックスが貼られずに済んでいる」というわけではなく、後者で上書きされただけということですね。

###【2】について

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

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

投稿2020/06/24 01:23

coffeejelly

総合スコア71

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

sazi

2020/06/24 02:21

リレーション上では外部キーの関係はあるが、外部キーの設定は行いたくないというのはよくありますね。 ER図の作成ツールなどで、DDL文出力時に「外部キーを含めいない」というオプションがあるのは、この要件に応えるものですし。
coffeejelly

2020/06/26 22:43

確かに、気持ちはわかりますよね。 ツールは知りませんでした。詳しくありがとうございます。
mikan123

2020/06/27 03:01

長文質問失礼いたしました。ご丁寧にありがとうございます。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問