副問い合わせの限界値はどれくらいなのでしょうか？

質問

副問い合わせの限界値はどれくらいなのでしょうか？　

現在下記のようなＳＱＬを考えています。

SELECT *
FROM A_TABLE
WHERE a_field in ( SELECT a_field
FROM B_TABLE
WHERE b_field := ?c )
;

※（便宜上、外部変数の指定を[:= ?c]としました）

B_TABLEの検索に引っかかったデータをinの条件にしてA_TABLEを検索するのですが、
B_TABLEの検索で出力される結果（サブクエリで出力される件数）が
現在の理論値は平均でおおよそ1000件、最大10万件になる予定です。

これに対し、10万件での問い合わせに副問い合わせの実行はmysqlで可能なのでしょうか？　
現状のテスト環境では10万件のテストデータを用意できない状況です・・・。

また、どなたか副問い合わせでのサブクエリ出力件数の限界値をご存知の方がいらっしゃいましたらご教授頂ければ幸いです。

宜しくお願い致します。

回答No.2

moousi · ベストアンサー

サブクエリの限界値とのことですが、実質限界はないと考えていいと思います。
プライマリーキーがあれば、intのプライマリーキーであれば、int限界値が限界ですが、それより多くの値を出力できるはずです。

サブクエリは、まずメモリ上でのテンポラリ・テーブル作成を試みます。
tmp_table_size値、あるいはmax_heap_table_sizeを超えると、ファイル上にテンポラリ・テーブルを作成します。
そのサイズは、OSのtmpdirのサイズを超えない限り作成可能です。

しかし、それよりも低い限界値はユーザーの我慢です。

WHERE b_field := ?c
で固定値を指定しているにも関わらず、１０万件ということは相当遅いレスポンスなのでは？と思ってしまいます。

お礼

ほぼ限界はなく、後はパフォーマンスとチューニングの問題なのですね。
回答ありがとうございます。

現在は、Ano.1さんの回答の御礼に書いたようなSQLで回避しようと思います。

補足

お二方とも、回答ありがとうございました。
回答を締め切らせて頂きます。

ポイントはお二人に差し上げたいのですが、
やむを得ずこの形にしました。
再度質問させて頂く際には宜しくお願い致します。

回答No.1

yambejp

インデックスさえきちんと設定されていればサブクエリでもいけそうな気がしますが
場合によってはinner joinなどで代替したほうが現実的かもしれません。
10万件単位のマッチが必要となるとチューニングを考えるとデータを併せてしまわないと
パフォーマンスが出ない可能性もあります

お礼

御礼遅くなって申し訳ありません。

件数がボトルネックでパフォーマンスが出ないですか・・・。
そうしたら、

SELECT *
FROM A_TABLE
WHERE a_field in ( SELECT a_field
FROM B_TABLE
WHERE b_field := ?c
ORDER BY CREATE_DATE
LIMIT 0 , 100
)
;

などで100件絞込みを行うという形でいきます。
回答ありがとうございました。