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

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

ただいまの
回答率

87.94%

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

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 670

score 1

前提

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文になります。

create_wp_firsts();
create_wp_seconds();

function create_wp_firsts() {
    global $wpdb;
    $sql = "CREATE TABLE IF NOT EXISTS wp_firsts (

        -- カラムを設定
        ID BIGINT(20) UNSIGNED NOT NULL,
        title VARCHAR(10) NOT NULL,

        -- 主キーを設定
        PRIMARY KEY (ID)

    );";
    add_option($table."_version", '1.0');
    $wpdb->query($sql);
}

function create_wp_seconds() {
    global $wpdb;
    $sql = "CREATE TABLE IF NOT EXISTS wp_seconds (

        -- カラムを設定
        ID BIGINT(20) UNSIGNED NOT NULL,
        first_ID BIGINT(20) UNSIGNED NOT NULL ,
        sentence VARCHAR(100) NOT NULL,

        -- 主キーを設定
        PRIMARY KEY (ID),

        -- インデックスを設定
        INDEX idx_wp_seconds_01(sentence),

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

    );";
    add_option($table."_version", '1.0');
    $wpdb->query($sql);
}

試したこと

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

CREATE TABLE `wp_seconds` (
 `ID` bigint(20) unsigned NOT NULL,
 `first_ID` bigint(20) unsigned NOT NULL,
 `sentence` varchar(100) NOT NULL,
 PRIMARY KEY (`ID`),
 KEY `idx_wp_seconds_01` (`sentence`),
 KEY `fk_wp_seconds_01` (`first_ID`),
 CONSTRAINT `fk_wp_seconds_01` FOREIGN KEY (`first_ID`) REFERENCES `wp_firsts` (`ID`),
 CONSTRAINT `wp_seconds_ibfk_1` FOREIGN KEY (`first_ID`) REFERENCES `wp_firsts` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


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

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

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

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

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 1

checkベストアンサー

+4

【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 11:21

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

    キャンセル

  • 2020/06/27 07:43

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

    キャンセル

  • 2020/06/27 12:01

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

    キャンセル

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

  • ただいまの回答率 87.94%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る