H.3. Restricciones en subconsultas

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

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

Pero ésto no es soportado:

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

La razón de soportar comparación entre registros para IN pero no para otros es que el IN fue inplementado reescribiendolo como una secuencia de comparaciones = y operaciones AND. Este comportamiento no puede ser usado para ALL, ANY, o SOME.

Constructores de Registros no son bien optimizados. Las siguientes dos expresiones son equivalentes, pero solo 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 tipicpo para el pobre desempeño de el IN es cuando una subconsulta regresa un numero pequeño de registros pero la consulta de afuera regresa un numero grande de registros para ser comparados con el resultado de la consulta.

Las subconsultas en la cláusula FROM no pueden ser subconsultas correlativas. Estás son materializadas (ejecutadas para producir un resultado) antes de evaluar la consulta de afuera, asi que éstas no pueden ser evaluadas por registro de la consulta de afuera.

En general, no puede modificar una tabla y una seleccion de la misma tabla en una subconsulta. Por ejemplo, esta limitación aplica a las sentencias de las siguientes formas:

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 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 ...);

Aqui la prohibición no aplica debido a que la subconsulta en la cláusula FROM es materializada como una tabla temporal, asi que los registros relevantes en t ya han sido seleccionados en el tiempo que la actualización de t toma lugar.

El optimizador es más maduro para uniones que para subconsultas, asi que en el mayor de los casos las sentencias que usan subconsultas pueden ser ejecutadas más eficientemente si las reescribe como una union.

Una excepción ocurre para el caso donde una subconsulta IN puede ser reescrita como una union(join) SELECT DISTINCT. Ejemplo:

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

La setencia puede ser reescrita como sigue:

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

Pero en este caso, la union(join) requiere una operación exxtra DISTINCT y no es más eficiente que la subconsulta.

Futura optimizacion posible: MySQL no reescribe el orden de la union(join) para la evaluacion de subconsulta. En algunos casos, una subconsulta puede ser ejecutada más eficientemente si MySQL la reescribe como una union(join). Este daria a el optimizador mayor posibilidad de elegir entre mas planes de ejecución. Por ejemplo, este puede decidir si leer una tabla o la otra primero.

Ejemplo:

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

Para esa 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 y inner_table tiene pocos registros, la consulta probablemente no será tan rapida como pudiera ser.

La consulta anterior podria ser reescrita asi:

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

En éste caso, podemos explorar la tabla pequeña (inner_table) y buscar registros en outer_table, lo cual seria más rapida si existe un indice en (ot.a,ot.b).

Futura optimizacion posible: Una subconsulta correlativa es evaluada para cada registro de la consulta de afuera. Una mejor solución es que si el registro de afuerano cambia para el registro anterior, no evaluar la subconsulta otra vez. En vez de esto usar su previo resultado.

Futura optimizacion posible: Una subconsulta en la clausula FROM es evaluada materializando el resultado dentro de una tabla temporal, y esta tabla no usa indices. Esto no permite el uso de indices en comparación con otras tablas en la consulta, aunque pudiera ser útil.

Futura optimizacion posible: Si una subconsulta en la cláusula se asemeja a una vista el algoritmo puede ser aplicado, reescribir la consulta, aplicar el algoritmo resultante y de esa manera los indices pueden ser usados. Las siguientes sentencias contienen una consulta como ésta:

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

La stentencia puede ser reescrita con una union(join) como ésta:

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

Este tipo de reescritura proveria dos beneficios

Futura optimizacion posible: Para los IN, = ANY, <> ANY, = ALL, y <> ALL con subconsultas no correlativas, usar una tabla hash en memoria para un resultado o una tabla temporal con un idicie para los resultados mas 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, podriamos crear una tabla temporal:

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

Entonces, para cada renglon en big_table, hacer un ciclo en t basado en bt.non_key_field.