後に修正される既知のバグ :ALL 、ANY 、または SOME を使用して NULL 値をサブクエリと比較し、サブクエリが空の結果を戻す場合、その比較は TRUE もしくは FALSE よりも、NULL の非標準結果を評価した可能性があります。

サブクエリの外側のステートメントが次のどれかである可能性があります :SELECT 、INSERT 、UPDATE 、DELETE 、SET または DO

IN のサブクエリの最適化は、= オペレータ、または IN(value_list) 構文に対する最適化ほど効果的ではありません。

貧弱な IN サブクエリの動作の一般的なケースで、サブクエリが少数の行を戻すのに対し、外側のクエリは多数の行をサブクエリの結果と比較するために戻します。

その問題は、IN サブクエリを使用するステートメントでは、最適化機能がそれを相関サブクエリとして書き換えるということにあります。非相関サブクエリを使用する次のステートメントを検討してください :

SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2);

最適化機能がステートメントを相関サブクエリに書き換えます :

SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);

内側と外側のクエリが M および N 行を戻す場合、それぞれ、実行時間は非相関サブクエリに対してと同様に、O(M+N) ではなく、O(M×N) の順になります。

それはつまり、IN サブクエリが、サブクエリが戻す同じ値をリストアップする IN(value_list) 構文を使って書かれたクエリよりも、格段に実行速度が遅い場合があることを示しています。

基本的に、テーブルを改変したり、サブクエリの同じテーブルから選択することはできません。例えば、この制限は次のフォームのステートメントに適用されます :

DELETE FROM t WHERE ... (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);
{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);

例外 :FROM 句にある改変されたテーブルにサブクエリを使用する場合、前述の禁止事項は適用されません。例 :

UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...) AS _t ...);

FROM 句のサブクエリからの結果が一時テーブルとして保存されるため、禁止事項は適用されず、t の該当する行が、t へのアップデートまでには選択されているということになります。

行の比較のオペレーションは一部のみサポートされています :

つまり、n タプルの行を戻すサブクエリには、次がサポートされています :

(val_1, ..., val_n) IN (subquery)

しかし、次はサポートされていません :

(val_1, ..., val_n) op {ALL|ANY|SOME} (subquery)

IN の行の比較がサポートされているのに、他はされていない理由は、IN が = 比較および AND オペレーションへ書き換えることによって実装されているためです。この方法は、ALL 、ANY 、または SOME には使うことができません。

行コンストラクタの最適化は的確に行われていません。次のふたつの式は等価ですが、ふたつ目のみが最適化されます :

(col1, col2, ...) = (val1, val2, ...)
col1 = val1 AND col2 = val2 AND ...

FROM 句のサブクエリは相関サブクエリにはなりえません。それらは外側のクエリを評価する前に出力 ( 結果セットを生成するために実行される ) されているので、外側のクエリの行ごとに評価を受けることはできません。

最適化機能は、サブクエリに対してより結合に対するほうが完成度が高く、そのため多くの場合は、サブクエリを使用するステートメントを結合として書き換えるほうが、より効率的に実行されます。

IN サブクエリが SELECT DISTINCT 結合として書き換えられる場合には例外が生じます。例 :

SELECT col FROM t1 WHERE id_col IN (SELECT id_col2 FROM t2 WHERE condition);

そのステートメントは次のように書き換えられます :

SELECT DISTINCT col FROM t1, t2 WHERE t1.id_col = t2.id_col AND condition;

しかしこの場合は、追加の DISTINCT オペレーションが結合に必要になり、サブクエリより効率的とは言えません。

可能な今後の最適化 :MySQL はサブクエリ評価のために結合順を書き換えることはしません。場合によっては、MySQL がサブクエリを結合として書き換えたほうが効率的に実行されるようです。これをふまえれば、最適化機能により豊富な実行計画の選択肢を与えることができます。例えば、特定のテーブルを先に読み込むか、他を先にするか決定することが可能です。

例 :

SELECT a FROM outer_table AS ot
WHERE a IN (SELECT a FROM inner_table AS it WHERE ot.b = it.b);

そのクエリでは、MySQL は常に outer_table を先にスキャンし、それから inner_table の各行でサブクエリを実行します。outer_table の行数が多く、inner_table の行数が少ない場合は、クエリの速度が比較的落ちるでしょう。

上記のクエリは次のように書くこともできます :

SELECT a FROM outer_table AS ot, inner_table AS it
WHERE ot.a = it.a AND ot.b = it.b;

この場合、小さなテーブル ( inner_table ) をスキャンし、outer_table で行を検査することで、(ot.a,ot.b) にインデックスがあれば速くなります。

可能な今後の最適化 :相関サブクエリは、外側のクエリのそれぞれの行に対して評価されます。よりよい方法としては、外側の行の値がその前の行と同じである場合、サブクエリを 再度評価せず、かわりに前の結果を使用します。

可能な今後の最適化 :FROM 句のサブクエリは、結果を一時テーブルに出力することによって評価されます。また、そのテーブルはインデックスを使用しません。これにより、クエリの他のテー ブルとの比較にインデックスを使えると便利ではありますが、それは許可されていません。

可能な今後の最適化 :FROM 句のサブクエリが、組合せアルゴリズムの適用が可能なビューに類似している場合、クエリを書き換えて組合せアルゴリズムを適用すると、インデックスが使用できるようになります。次のステートメントにはその種のサブクエリが含まれています :

SELECT * FROM (SELECT * FROM t1 WHERE t1.t1_col) 
         AS _t1, t2 WHERE t2.t2_col;

このステートメントは次のように、結合として書き換えることができます :

SELECT * FROM t1, t2 WHERE t1.t1_col AND t2.t2_col;

このタイプの書き換えには、ふたつの利点があります :

可能な今後の最適化 : 非相関サブクエリを持つ IN 、= ANY 、<> ANY 、= ALL 、および <> ALL には、メモリ内ハッシュを結果に使用するか、大きな結果にはインデックスのある一時テーブルを使用してください。例 :

SELECT a FROM big_table AS bt
WHERE non_key_field IN (SELECT non_key_field FROM table WHERE condition)

この場合、一時テーブルを作成することもできます :

CREATE TABLE t (key (non_key_field))
(SELECT non_key_field FROM table WHERE condition)

そして、big_table の各行では、bt.non_key_field に基づいて、t でキー検査を行ってください。