Ein bekannter Bug, der noch behoben wird: Wenn Sie einen NULL-Wert mithilfe von ALL, ANY oder SOME mit einer Unterabfrage vergleichen und die Unterabfrage eine Ergebnismenge zurückgibt, kann der Vergleich das nichtstandardmäßige Ergebnis NULL anstatt TRUE oder FALSE zurückliefern.

Die äußere Anweisung einer Unterabfrage kann ein SELECT, INSERT, UPDATE, DELETE, SET oder DO sein.

Im Allgemeinen können Sie eine Tabelle nicht in einer Unterabfrage modifizieren und zugleich mit einem Select abfragen. Diese Beschränkung gilt beispielsweise für Anweisungen der folgenden Form:

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

Ausnahme: Das obige Verbot gilt nicht, wenn Sie eine Unterabfrage für die modifizierte Tabelle in der FROM-Klausel verwenden. Beispiel:

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

Das Verbot ist hier deshalb außer Kraft gesetzt, weil die Unterabfrage in der FROM-Klausel von einer temporären Tabelle verkörpert wird, sodass die relevanten Zeilen in t zu dem Zeitpunkt, da das Update in t stattfindet, von dem Select bereits abgerufen worden sind.

Zeilenvergleichsoperationen werden nur teilweise unterstützt:

Mit anderen Worten, für eine Unterabfrage, die Zeilen von n--Tupeln zurückgibt, wird Folgendes unterstützt:

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

Doch dieses wird nicht unterstützt:

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

Der Grund, weshalb Zeilenvergleiche für IN unterstützt werden, aber für die anderen Klauseln nicht, besteht darin, dass IN aufgrund seiner Implementierung den Vergleich als eine Serie von =-Vergleichen und AND-Operationen neu schreibt. Dieser Ansatz kann für ALL, ANY oder SOME nicht verwendet werden.

Zeilenkonstruktoren sind nicht gut optimiert. Die folgenden beiden Ausdrücke sind äquivalent, aber nur der zweite kann optimiert werden:

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

Die Unterabfragenoptimierung ist für IN weniger effizient als für den =-Operator.

Ein typisches Beispiel für die schlechte Performance von IN tritt ein, wenn die Unterabfrage nur wenige, die übergeordnete Abfrage hingegen viele Zeilen zurückgibt, die mit den Ergebnissen der Unterabfrage verglichen werden müssen.

Unterabfragen in der FROM-Klausel dürfen keine korrelierten Unterabfragen sein. Sie werden materialisiert (ausgeführt, um eine Ergebnismenge zu erstellen), bevor die äußere Abfrage ausgeführt wird. Daher können sie nicht pro Zeile der äußeren Abfrage ausgewertet werden.

Da der Optimierer für Joins besser als für Unterabfragen gerüstet ist, lassen sich oft Anweisungen mit Unterabfragen effizienter ausführen, wenn man sie als Joins umformuliert.

Eine Ausnahme bildet der Fall, in dem eine IN-Unterabfrage als SELECT DISTINCT-Join umformuliert werden kann. Beispiel:

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

Diese Anweisung kann man folgendermaßen umformulieren:

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

Doch hier erfordert der Join eine zusätzliche DISTINCT-Operation und ist deswegen nicht effizienter als die Unterabfrage.

Eine mögliche zukünftige Optimierung könnte darin bestehen, dass MySQL die Join-Reihenfolge nicht für die Auswertung der Unterabfrage neu schreibt. In manchen Fällen ließe sich eine Unterabfrage effizienter ausführen, wenn MySQL sie als Join umformulierte. Dies gäbe dem Optimierer die Gelegenheit, zwischen mehreren Ausführungsplänen auszuwählen. Er könnte beispielsweise entscheiden, welche von zwei Tabellen er als Erste liest.

Beispiel:

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

Für diese Anfrage würde MySQL immer zuerst die outer_table scannen und dann die Unterabfrage für jede Zeile auf der inner_table ausführen. Wenn outer_table viele und inner_table wenige Zeilen hat, liefe die Anfrage nicht so schnell, wie sie könnte.

Die obige Anfrage könnte man folgendermaßen umformulieren:

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

In diesem Fall scannen wir zuerst die kleine Tabelle (inner_table) und schauen danach die Zeilen in der großen outer_table nach. Das geht ganz schnell, wenn wir einen Index auf (ot.a,ot.b) haben.

Mögliche zukünftige Optimierung: Eine korrelierte Unterabfrage wird für jede Zeile der äußeren Abfrage ausgewertet. Es wäre besser, die Unterabfrage nicht erneut auszuwerten, wenn die Werte der äußeren Zeile immer noch dieselben sind wie in der vorherigen Zeile. Stattdessen könnte man das vorherige Ergebnis wiederverwenden.

Mögliche zukünftige Optimierung: Eine Unterabfrage in der FROM-Klausel wird ausgewertet, indem ihr Ergebnis in einer temporären Tabelle festgehalten wird, die keine Indizes verwendet. So können auch bei Vergleichen mit anderen Tabellen in der Abfrage keine Indizes eingesetzt werden, auch dann nicht, wenn es sinnvoll wäre.

Mögliche zukünftige Optimierung: Wenn eine Unterabfrage in der FROM-Klausel einer View ähnelt, auf die der Merge-Algorithmus angewendet werden kann, formulieren Sie die Anfrage um und wenden den Merge-Algorithmus an, damit Indizes genutzt werden können. Die folgende Anweisung enthält eine solche Unterabfrage:

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

Die Anweisung kann folgendermaßen als Join geschrieben werden:

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

Diese Umformulierung hat zwei Vorteile:

Mögliche zukünftige Optimierung: Für IN, = ANY, <> ANY, = ALL und <> ALL mit nichtkorrelierten Unterabfragen könnte man für das Ergebnis einen speicherresidenten Hash oder, bei größeren Ergebnismengen, eine temporäre Tabelle mit Index verwenden. Beispiel:

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

In diesem Fall würden wir eine temporäre Tabelle anlegen:

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

Dann könnten wir für jede Zeile in big_table einen Schlüssel-Lookup in t anhand von bt.non_key_field machen.