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

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

ただいまの
回答率

90.50%

  • MySQL

    6995questions

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

より良いSQLの書き方

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 4
  • VIEW 1,456

jk233

score 36

次の2つのテーブルがあるとします。
(場所マスタ)
場所ID 場所名
0        野菜室
1        冷蔵
2        冷凍

(データ)
データID 場所ID 食品名 数量 削除フラグ
0         0       とまと    3      1
1         1       プリン    2      1
2         1       豚肉      4      0
3         1       牛乳      1      0

次の結果を得たいです。
データID 場所名 食品名 数量
0         野菜室   NULL     NULL
1         冷蔵     豚肉     4
1         冷蔵     牛乳     1
2         冷凍     NULL     NULL

以下のSQLを考えましたが、より良い方法があったら教えて下さい。
JK_BASHODATAにフルスキャンがかかってしまうので性能的によくないのかなと思いました。

SELECT bashoId, bashoName, D.itemName, D.suryo
FROM JK_BASHOMAS
LEFT JOIN (SELECT * FROM JK_BASHODATA WHERE sakujoFlg=0) D USING(bashoId)

【参考】

CREATE TABLE JK_BASHOMAS (
    bashoId int not null primary key,
    bashoName varchar(255) not null
);
CREATE TABLE JK_BASHODATA (
    dataId int not null primary key,
    bashoId int not null,
    itemName varchar(255) not null,
    suryo int not null,
    sakujoFlg boolean not null
);
INSERT INTO JK_BASHOMAS (bashoId, bashoName) VALUES (0, '野菜室');
INSERT INTO JK_BASHOMAS (bashoId, bashoName) VALUES (1, '冷蔵');
INSERT INTO JK_BASHOMAS (bashoId, bashoName) VALUES (2, '冷凍');
INSERT INTO JK_BASHODATA (dataId, bashoId, itemName, suryo, sakujoFlg) VALUES (0, 0, 'とまと', 3, 1);
INSERT INTO JK_BASHODATA (dataId, bashoId, itemName, suryo, sakujoFlg) VALUES (1,1,'プリン',2,1);
INSERT INTO JK_BASHODATA (dataId, bashoId, itemName, suryo, sakujoFlg) VALUES (2,1,'豚肉',4,0);
INSERT INTO JK_BASHODATA (dataId, bashoId, itemName, suryo, sakujoFlg) VALUES (3,1,'牛乳',1,0);
  • 気になる質問をクリップする

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 3

checkベストアンサー

+2

 Before

SELECT bashoId, bashoName, D.itemName, D.suryo
FROM JK_BASHOMAS
LEFT JOIN (SELECT * FROM JK_BASHODATA WHERE sakujoFlg=0) D USING(bashoId);
 実行結果
bashoId    bashoName    itemName    suryo
0    野菜室    (null)    (null)
1    冷蔵    牛乳    1
1    冷蔵    豚肉    4
2    冷凍    (null)    (null)
 EXPLAIN
id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    PRIMARY    JK_BASHOMAS    ALL    (null)    (null)    (null)    (null)    3    (null)
1    PRIMARY    <derived2>    ref    <auto_key0>    <auto_key0>    4    db_9_a7aa9.JK_BASHOMAS.bashoId    2    (null)
2    DERIVED    JK_BASHODATA    ALL    (null)    (null)    (null)    (null)    4    Using where

 After

SELECT B.bashoId, B.bashoName, D.itemName, D.suryo
FROM JK_BASHOMAS AS B
LEFT JOIN JK_BASHODATA AS D ON B.bashoId = D.bashoId AND D.sakujoFlg = 0 
ORDER BY B.bashoId;
 実行結果
bashoId    bashoName    itemName    suryo
0    野菜室    (null)    (null)
1    冷蔵    豚肉    4
1    冷蔵    牛乳    1
2    冷凍    (null)    (null)
 EXPLAIN
id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    B    ALL    (null)    (null)    (null)    (null)    3    Using temporary; Using filesort
1    SIMPLE    D    ALL    (null)    (null)    (null)    (null)    4    Using where; Using join buffer (Block Nested Loop)

これでサブクエリを実行しなくて済むようになります。
あとはKosuke_Shibuya様の回答にあるように適切にインデックスを付けてやれば、十分な性能を得られると思います。

 EXPLAIN(インデックス追加後)
CREATE TABLE JK_BASHODATA (
    dataId int not null primary key,
    bashoId int not null,
    itemName varchar(255) not null,
    suryo int not null,
    sakujoFlg boolean not null,

    index (bashoId), 
    index (sakujoFlg)
);
id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    B    index    (null)    PRIMARY    4    (null)    3    (null)
1    SIMPLE    D    ref    bashoId,sakujoFlg    bashoId    4    db_9_9c8de.B.bashoId    1    Using where

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/06/13 13:45

    ON句にsakujoFlg=0を書くという発想ができていませんでした…
    ありがとうございました。

    キャンセル

+1

JK_BASHODATA のカラム、bashoId、sakujoFlg にインデックスをつければよろしいのでは?

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

正直数百、数千レコード程度であれば、JK_BASHODATA全件の中からsakujoFlgで絞り込む処理はそれほど負荷ではないと思います。しかし、逆にそのJK_BASHODATAが数十万、数百万件あるというのであれば、そもそもsakujoFlgで削除したあとも残し続けていることのほうが負荷になると思います。sakujoFlg=1のデータを定期的に別テーブルにバックアップ後DELETEするなどし、普段アクセスするJK_BASHODATAの件数を減らしてやる等の対処がよろしいかと思います。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/06/13 13:49

    JK_BASHODATAは数十万件を想定しています。
    削除されるのは全体の1%くらいです。
    DELETE時にトリガで別テーブルにコピーするのが参照時の性能的に一番いいのかなと思いましたが、今回のケースではテーブルが増えてしまうデメリットの方が大きいと判断しました。
    回答ありがとうございました。

    キャンセル

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

  • MySQL

    6995questions

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