子レコードをすべて含み、余分な子レコードを含まない、親レコードを特定するSQL

Question

前提条件

• 異なるサービスを組み合わせたサービスセットがある
• order_code、priceは取引先が指定
• service_set_relテーブルは考えられるパターンがほぼ網羅されている(パターンに重複はない)
• service_set_relテーブルは 数万件

実現したいこと

• 単品のサービスをいくつか選択し、サービスセットのorder_codeとpriceが特定できる効率的なSQL文が知りたいです。
• 1ヵ月分一括請求処理するので、数千回～数万回、下記SQLをループ処理する必要があります。
  一応、動作しますが、Full Table Scanが入り、遅いようです。
  これがベストなのか、他に良い方法があるのか、知りたいです。

services テーブル

id	name
1	service1
2	service2
3	service3
4	service4
5	service5
6	service6

service_set テーブル

id	set_name	order_code	price
1	set1	A001	456
2	set2	A002	200
3	set3	A003	700
4	set4	AF004	567
5	set5	B005	123
6	set6	A006	345

service_set_rel テーブル

set_id	service_id
1	2
1	3
1	4
2	1
2	4
3	4
3	2
3	1
3	3
4	4
4	3
4	2

試してみたSQL

例: service_id 4,2,3 を3つ含む、service_setを特定する場合

SELECT order_code,price
FROM service_set AS s_set
WHERE
    s_set.id IN (SELECT set_id FROM service_set_rel WHERE service_id IN (4, 2, 3)) 
    AND s_set.id NOT IN (SELECT set_id FROM service_set_rel WHERE service_id NOT IN (4, 2, 3))
    AND s_set.id IN (SELECT set_id FROM service_set_rel GROUP BY set_id HAVING COUNT(service_id) = 3)

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

MySQL 8.0

Answer 1

何回も引くというなら、service_set_rel をgroup_concatした中間テーブルを作るとか。

create temporary table foo
select group_concat( service_id order by service_id) as service_ids,set_id
from service_set_rel group by set_id

service_idsにindex貼って、where service_ids=‘2,3,4’で引く。
あんまりservice_idsが長くなる(1024文字だから、10桁idが100個あるとか)ようだと問題になるけど、、

Comments

[tomo-oniichan]

ありがとうございます。
テーブル自体の更新頻度が少なく、selectすることの方が多いので、今回はservice_idsを作成しようと思います。