Subconsultas vs uniones

Está ejecutando la subconsulta una vez por cada fila mientras que la combinación ocurre en los índices.

He aquí un ejemplo de cómo se evalúan las subconsultas en MySQL 6.0.

El nuevo optimizador convertirá este tipo de subconsultas en joins.

Ejecute el plan explain en cada versión, le dirá por qué.

Antes de que las consultas se ejecuten contra el conjunto de datos que se ponen a través de un optimizador de consultas, el optimizador intenta organizar la consulta de tal manera que pueda eliminar tantas tuplas (filas) del conjunto de resultados tan rápido como pueda. A menudo, cuando utiliza subconsultas (especialmente las malas), las tuplas no se pueden eliminar del conjunto de resultados hasta que la consulta externa comience a ejecutarse.

Sin ver la consulta es difícil decir lo que era tan malo sobre el original, pero mi conjetura sería que era algo que el optimizador no podía hacer mucho mejor. Ejecutar 'explain' le mostrará el método de optimizadores para recuperar los datos.

Generalmente es el resultado de que el optimizador no puede averiguar que la subconsulta se puede ejecutar como una combinación, en cuyo caso ejecuta la subconsulta para cada registro en la tabla en lugar de unir la tabla en la subconsulta contra la tabla que está consultando. Algunas de las bases de datos más "emprendedoras" son mejores en esto, pero todavía lo echan de menos a veces.

Esta pregunta es algo general, así que aquí hay una respuesta general:

Básicamente, las consultas toman más tiempo cuando MySQL tiene toneladas de filas para ordenar.

Haz esto:

Ejecute una EXPLICACIÓN en cada una de las consultas (la JOIN'ed, luego la Subqueried), y publique los resultados aquí.

Creo que ver la diferencia en la interpretación de MySQL de esas consultas sería una experiencia de aprendizaje para todos.

La subconsulta where tiene que ejecutar 1 consulta por cada fila devuelta. La unión interna solo tiene que ejecutar 1 consulta.

Mire el plan de consulta para cada consulta.

Donde en y Join puede típicamente implementarse usando el mismo plan de ejecución, por lo que típicamente no hay ninguna velocidad de cambio entre ellos.

Optimizer no hizo un muy buen trabajo. Por lo general, se pueden transformar sin ninguna diferencia y el optimizador puede hacer esto.

La subconsulta probablemente estaba ejecutando un "análisis completo de tabla". En otras palabras, no usar el índice y devolver demasiadas filas que el Where de la consulta principal necesitaba filtrar.

Solo una suposición sin detalles, por supuesto, pero esa es la situación común.

Con una subconsulta, debe volver a ejecutar la 2a SELECCIÓN para cada resultado, y cada ejecución normalmente devuelve 1 fila.

Con una combinación, la 2a SELECCIÓN devuelve muchas más filas, pero solo tiene que ejecutarla una vez. La ventaja es que ahora puede unirse a los resultados, y unir relaciones es en lo que se supone que una base de datos es buena. Por ejemplo, tal vez el optimizador pueda detectar cómo aprovechar mejor un índice ahora.

No es tanto la subconsulta como la cláusula IN, aunque las uniones están en la base de al menos el motor SQL de Oracle y se ejecutan extremadamente rápido.

Tomado del Manual de Referencia ( 14.2.10.11 Reescribir Subconsultas como Joins):

Una UNIÓN [EXTERNA] IZQUIERDA puede ser más rápida que una subconsulta equivalente porque el servidor podría ser capaz de optimizarla mejor, un hecho que no es específico solo del servidor MySQL.

Así que las subconsultas pueden ser más lentas que las UNIONES [EXTERNAS] IZQUIERDAS.