WHEREなどの条件が多い場合、どうすべきか？

質問

複数のテーブルをleft joinで繋いで、カラムをエクセルのようなシートとしてWebで表示しています。
各カラムは、VERCHAR、DECIMAL、INT（SMALLINT～MIDIUMINT）の型を持っています。

エクセルのシートのような感じなので、エクセルのオートフィルタのような機能を付けたいと考えています。
各カラムをAND条件で指定できるやつです。

ですが、そもそもカラムの数が30ほどあります。
このような場合でもANDやBETWEENを使ってひたすらWHERE条件やCASEなどで抽出するようにするのでしょうか？

DECIMALやINTの場合は条件を○○（最低値）～○○（最高値）のようにします。

VARCHARの場合は、選べる文字列が複数（数十程度）あり、そこから選べるようにします。
例えば、東京駅。渋谷駅、新宿駅、品川駅、横浜駅・・・などがあって、フィルターしたい文字列だけ選びます。
たぶんINを使った感じになります。

聞きたいのは、WHERE　○○　AND　○○　AND　○○　AND　・・・のようにひたすら条件を作っていくしかないのでしょうか？

どうぞよろしくお願い致します。

回答No.3

JaneDue

書式としては
col1 = 'A' AND col2 = 'B' AND col3 = 'C'
なら
WHERE (col1,col2,col3) IN (('A','B','C'))

ORならセットで複数指定して
WHERE
　(col1,col2,col3)
IN (
　('A','A','A'),
　('B','B','B'),
　('A','B','C')
)

という書き方もあるにはあります。パフォーマンスはEXPLAINしてみて。
ご参考まで

回答No.2

nda23

>ひたすらWHERE条件や～
そうすべきです。
WHERE句が最も速く抽出条件を絞ります。
HAVINGやJOINでも絞れますが、処理した結果に
ついて条件をかけるので、遅くなります。

それと、30くらいの条件などは通常業務ではよく
出てくる範囲です。尚、サブクエリが使えるDBなら、
サブクエリを入れ子にすると、ANDで条件を結合
するより効率があがります。
(1)SELECT * FROM XX WHERE a=1 AND b=2
(2)SELECT * FROM (SELECT * FROM XX WHERE a=1)
　WHERE b=2
後者の方が効率が良いということです。特に内側のクエリが
インデックスによる条件抽出なら、外側のクエリが対象とする
レコード群はかなり数が絞り込まれている状態からスタート
できる訳です。

尚、抽出したレコードを更新する場合、JOINしているクエリが
対象だと制約があります。1:1になるか、1:NのN側が保証され
ないと更新できません。保証できない時は対象テーブルと同じ
レイアウトの一時テーブルに抽出レコードを記録し、これと
主キーを使ってINNER JOINします。同じキー構成なので必ず
1:1になります。一時テーブルはセッション単位に独立している
ため、他のスレッドと干渉しません。また、セッションが切れる
時に自動的に解放されるます。従って、トランザクション管理が
ないので高速ですし、後始末も不要です。

お礼

ありがとうございます！

＞そうすべきです。
＞WHERE句が最も速く抽出条件を絞ります。
＞HAVINGやJOINでも絞れますが、処理した結果に
＞ついて条件をかけるので、遅くなります。

そうなのですね・・・！ひたすらWHEREって効率が悪いのかと勘違いしていました。

＞尚、サブクエリが使えるDBなら、
＞サブクエリを入れ子にすると、ANDで条件を結合
＞するより効率があがります。

これは知りませんでした。

＞尚、抽出したレコードを更新する場合、JOINしているクエリが
対象だと制約があります。・・・

ありがとうございます。ここはどうやるのか不明だったのでたいへん参考になりました。

回答No.1

yambejp

質問の意図がよくわからないのですが・・・

where 条件1 and 条件2 and 条件3

という場合、条件1、2、3のそれぞれに相関関係がないなら
並行して条件を設定して絞り込むしかありません
条件同士なんらかの関係がある場合は、場合によっては
効率的なSQL文がかけるかもしれません

また正規化などきちんとされていれば
joinする際にinner joinで絞り込みができる場合もあります

いま与えられている命題ではその程度しか回答しようがありません

お礼

アドバイス頂きましてありがとうございます。

すみません、質問の意図は「30ものカラムの条件をANDで並列に書くことが効率的な方法なのでしょうか？」というものです。

あと質問に書き忘れましたが、このような検索（フィルタリング）をする場合、全てのカラムにインデックスを張る必要がありますか？

このフィルタリングをして抽出したレコードを更新する必要があります。

もしWHEREなどで条件に設定するカラムにインデックスがないと、UPDATE時にテーブル・ロックがかかるのですよね？
一度に数十万行を更新するのでできれば行ロックでUPDATEを済ませたいと思っています。

ちなみにInnoDBです。

＞また正規化などきちんとされていれば
＞joinする際にinner joinで絞り込みができる場合もあります

ありがとうございます。少し見直す必要もありますが、だいたい正規化できています。