MySQL EAVアンチパターンにおけるクラステーブル継承の複数条件

Question

前提・実現したいこと

MySQL初心者です。初めてDB設計を行います。
MySQLでログデータを管理し、Web上から検索可能なシステムを構築しております。
ログデータには「要素」があり、それぞれのログデータの要素数や要素項目はバラバラです。定期的にデータベースに格納されていきます。
ログデータ全体は50万件程度あります。後に示しますが、ここでいっている50万件はMainTableのidに相当します。

---システム要求と検討方針---
システム要求には、
要素の複数条件検索が可能であること、要素に数値が入ること、要素が追加されること。等があることを鑑みて、
最終的にはEAVアンチパターンのクラステーブル継承を採用致しました。

※典型的なEAVアンチパターンだと思いました。
※「要素の複数条件検索可能」という条件を満たすためには、EAV(KeyValuStore型)ではAND条件が厳しいと考えました。
Key-Value型で(Key-Value)の条件を複数している事例がほとんど見つからなかったです。

---テーブル---
クラステーブル継承したテーブルを下記に示します。mainテーブルはマスタテーブルです。
ログデータはクラステーブルに自動採番(Autoincrement)でデータが納入されていきます。
path_idでMainTableへ紐付けしています。
会社からはアクセスできないので、かなり簡略化と覚えている程度で書いています。

--MainTable--
CREATE TABLE main (
id BIGINT UNSIGNED PRIMARY KEY,
comment varchar(100),
);

--SpeedTable--
CREATE TABLE speeds (
auto_id BIGINT UNSIGNED AUTOINCREMENT, PRIMARY KEY,
path_id BIGINT UNSIGNED PRIMARY KEY,
value INT(11),
FOREIGN KEY (id) REFERENCES main(path_id )
);

--FuelTable--
CREATE TABLE fuels (
auto_id BIGINT UNSIGNED AUTOINCREMENT,PRIMARY KEY,
path_id BIGINT UNSIGNED PRIMARY KEY,
value INT(11),
FOREIGN KEY (id) REFERENCES main(path_id)
);

MainTableの例：
id / comment
1 / 車1
2 / 車2
3 / 車3
4 / 車4
5 / 車5
6 / 車6
7 / 車7

FuelTableの例：
auto_id / path_id / value
1 / 1 / 45
2 / 1 / 50
3 / 1 / 60
4 / 2 / 50
5 / 2 / 70
6 / 3 / 20
7 / 4 / 50
8 / 5 / 30
9 / 5 / 90

実際には上記テーブルのようなクラス継承テーブルが5コぐらいあります。

困っていること

要求にある、「複数条件による検索が可能であること。」を満たすために悩んでいます。
Web設計側から以下の質問を受けています。
例：SpeedTableの値が 20以上 かつ FuelTableの値が 50以上 のMainTableのCommentを取得するには、どのようなクエリが望ましいか。

発生している問題・エラーメッセージ

それぞれのクラステーブルで取得したレコードは必ずしも1つではないため、それぞれのテーブルでGroupByして、結合してHAVINGが使えない。
（条件に50以上などと指定された場合、2つ以上取得する可能性がある）

それぞれのクラステーブルでpath_idを取得できたとして、どのようにMainTableへ結合したらよいか分からない。

試したこと

クラステーブル継承ではなく、シングルテーブル継承を使って全ての組み合わせを1レコードずつ使うことも考えました。
ただ、ログデータを格納する機能側が譲らないため、かなり揉めてしまっています。
また、全ての組み合わせとなると「要素が追加されること」というシステム要求に対しても危険な設計なのかなと思っています。（組み合わせ爆発）

補足情報

MySQL 5.7.23 、 InnoDBを使用しています。

1～2週間程度の初心者です。どうか教えてください。かなり疲弊してしまいました。
システム要求は変えられそうにありません。

Answer 1

SpeedTableの値が 20以上 かつ FuelTableの値が 50以上 のMainTableのCommentを取得するには

それぞれの条件のテーブルで、path_idを求めて、mainの条件でandすれば良いかと思います。

select * from main 
where id in (select path_id from speeds where value>=20)
  and id in (select path_id from fuels where value>=50)

existsの方が効率が良いかもしれませんが、そこは実行計画で確認されて下さい。
追記

クラス別にテーブルを作らず、一つのテーブルに纏める方法も考えられます。
auto_id | path_id |category| value
|:--|:--:|--:|
1 | 1 | 'speed'| 45
2 | 1 | 'speed'| 50
3 | 1 | 'speed'| 60
4 | 2 | 'speed'| 50
5 | 2 | 'speed'| 70
6 | 3 | 'speed'| 20
7 | 4 | 'speed'| 50
8 | 5 | 'speed'| 30
9 | 5 | 'speed'| 90

上記のテーブルをelementsとすると、以下のような問合せになります。

select * from main 
where id in (select path_id from elements where category='speed' and value>=20)
  and id in (select path_id from elements where category='fuel' and value>=50)

Comments

[sazi]

speeds とfuels のprimaryにpath_idが含まれていますが、不要ですね。
うろ覚えで書かれているという事なので、本来は含まれていないのかもしれませんが。

[dorado]

ご回答ありがとうございます。早速仕事場にいったら試行してみます。
出てきた結果をinで合致させればよかったんですね・・・。

また、このやり方であればEAVでも適用できそうなので、時間があればEAVでも試してみたいと思います。要素追加要求があるので、今のままだとクラステーブルが50コぐらいに増えていってしまいますので・・・。

※path_idのprimaryは記載ミスでした。申し訳ありません。

[dorado]

回答通りの実行で期待通りの動きになりました。

追記して頂いた内容はEAV手法ですが、今回は最終的にEAV手法を採用することにしました。

ありがとうございました。