より良いSQLの書き方

Question

次の２つのテーブルがあるとします。
（場所マスタ）
場所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);

Answer 1

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

Comments

[jk233]

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

Answer 2

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

Answer 3

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

Comments

[jk233]

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