El siguiente bug es conocido y será reparado: Si se compara un valor NULL con una subconsulta usando ALL, ANY, o SOME, y la sentencia devuelve un resultado vacío, la comparación podría dar un resultado no estándar de NULL en vez de un TRUE o FALSE.

La sentencia de afuera de la subconsulta puede ser cualquiera de las siguientes: SELECT, INSERT, UPDATE, DELETE, SET, o DO.

Las operaciones de comparación de registros son soportadas sólo parcialmente:

En otras palabras, para una subconsulta que devuelve registros de n-tuplas, esto está soportado:

(val_1, ..., val_n) IN (subconsulta)

Pero esto no está soportado:

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

La razón por la que se soporta la comparación entre registros con IN pero no con los otros es que IN fue implementado reescribiéndolo como una secuencia de comparaciones = y operaciones AND. Esto mismo no puede realizarse con ALL, ANY, o SOME.

Los constructores de registros no están bien optimizados. Las siguientes dos expresiones son equivalentes, pero sólo la segunda puede ser optimizada:

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

La optimización de subconsultas para los IN no es tan efectiva como para los =.

Un caso típico del pobre rendimiento del IN es cuando una subconsulta devuelve un número pequeño de registros, pero la consulta de afuera regresa un número grande de registros para ser comparados con el resultado de la subconsulta.

Las subconsultas en la cláusula FROM no pueden ser subconsultas correlacionadas. Éstas son materializadas (ejecutadas para producir un resultado) antes de que se evalúe la consulta exterior, así que no pueden ser evaluadas por registro de la consulta exterior.

En general, no puede modificar una tabla y seleccionar de la misma en una subconsulta. Por ejemplo, esta limitación se aplica a sentencias del siguiente tipo:

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

Excepción: La prohibición precedente no se aplica si se usa una subconsulta para la tabla modificada en la cláusula FROM. Ejemplo:

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

Aquí la prohibición no se aplica porque una subconsulta en la cláusula FROM se materializa como una tabla temporal, así que los registros relevantes en t ya han sido seleccionados cuando la actualización de t ha tenido lugar.

El optimizador es más maduro para joins que para subconsultas, así que en la mayor parte de los casos las sentencias que usan subconsultas pueden ser ejecutadas más eficientemente si se reescriben como joins.

Una excepción a esta norma es el caso en que una subconsulta IN puede ser reescrita como una join SELECT DISTINCT. Ejemplo:

SELECT col FROM t1 WHERE id_col IN (SELECT id_col2 FROM t2 WHERE condición);

La sentencia puede ser reescrita como sigue:

SELECT DISTINCT col FROM t1, t2 WHERE t1.id_col = t2.id_col AND condición;

Pero en este caso el join requiere una operación DISTINCT extra y no es más eficiente que la subconsulta.

Futura optimización posible: MySQL no reescribe el orden del join para la evaluación de subconsultas. En algunos casos, una subconsulta puede ser ejecutada más eficientemente si MySQL la reescribe como un join. Esto daría al optimizador mayor posibilidad de elegir entre varios planes de ejecución. Por ejemplo, podría decidir si leer primero una tabla o la otra.

Ejemplo:

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

Para esra consulta, MySQL siempre busca outer_table primero y después ejecuta la subconsulta en inner_table para cada registro. Si outer_table tiene muchos registros e inner_table tiene pocos, la consulta probablemente no será tan rápida como pudiera ser.

La consulta anterior podría reescribirse así:

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

En este caso podemos explorar la tabla pequeña (inner_table) y buscar registros en outer_table, lo cual sería más rápido si existiera un índice en (ot.a,ot.b).

Futura optimización posible: Una subconsulta correlacionada se evalúa por cada registro de la consulta externa. Una mejor solución sería que no se evaluara la subconsulta nuevamente si el valor del registro exterior fuese igual al de la línea anterior. Se usaría en este caso el resultado previo.

Futura optimización posible: Una subconsulta en la cláusula FROM se evalúa materializando el resultado dentro de una tabla temporal, y esta tabla no usa índices. Esto no permite el uso de índices en comparación con otras tablas en la consulta, aunque pudiera ser útil.

Futura optimización posible: Si una subconsulta en la cláusula FROM se asemeja a una vista a la que se puede aplicar el algoritmo MERGE, reescribir la consulta, aplicar el algoritmo MERGE, para que se puedan utilizar los índices. La siguiente sentencia contiene una subconsulta de este tipo:

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

La sentencia puede ser reescrita con un join como éste:

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

Este tipo de reescritura comportaría dos beneficios

Futura optimización posible: Para los IN, = ANY, <> ANY, = ALL, y <> ALL con subconsultas no correlacionadas, usar una tabla hash en memoria para un resultado o una tabla temporal con un índice para los resultados más grandes. Ejemplo:

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

En este caso, podríamos crear una tabla temporal:

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

Entonces, para cada renglón en big_table, hacer un ciclo en t basado en bt.non_key_field.