es lo mismo aplicar una condicion en where que en el join?

[doylelives]

Hola a todos, tengo una duda que podria resolver una query bastante lenta que tengo.
Teniendo la query

Código PHP:

  SELECT t1.*, t2.* FROM tabla1 AS t1 
JOIN tabla2 AS t2 ON (t1.id=t2.id) 
WHERE t2.zipcode IN(123,234,456,789,159 );  
   

En vez de poner el filtro de zipcode en el where,¿ seria lo mismo a ponerlo en el momento del join???

Código PHP:

  SELECT t1.*, t2.* FROM tabla1 AS t1 
JOIN tabla2 AS t2 ON (t1.id=t2.id AND t2.zipcode IN(123,234,456,789,159 ));  
   

Mi limitado conocimiento diria que es igual, sin embargo al aplicarla en mi bd difieren los resultados.
la 1º query trae 100000 registros, mientras que la 2º solo 1500 registros.

porque?

[Nano_]

Saludos

Puntualmente estas son las diferencias

Es mas optimo utilizar

1.

Código MySQL:

SELECT t1.*, t2.* FROM tabla1 AS t1
JOIN tabla2 AS t2 ON (t1.id=t2.id)
 

que

Código MySQL:

SELECT t1.*, t2.* FROM tabla1 AS t1 WHERE tabla2 AS t2 ON (t1.id=t2.id)
 

Ya que en el 1. la tabla esta utilizando los indices de la llave primaria de la tabla mientras que en la segunda no por lo tanto lo haría mas lento

2.

Código MySQL:

SELECT t1.*, t2.* FROM tabla1 AS t1
JOIN tabla2 AS t2 ON (t1.id=t2.id)
WHERE t2.zipcode IN(123,234,456,789,159 );
 

Código MySQL:

SELECT t1.*, t2.* FROM tabla1 AS t1
JOIN tabla2 AS t2 ON (t1.id=t2.id AND t2.zipcode IN(123,234,456,789,159 ));
 

La diferencia de colocar la condición en el WHERE o en el ON, son cosas totalmente distintas ya que en el ON se utiliza solamente para proporcionar la relación entre tablas mientras que el where se asegura que esa consulta siempre se cumpla. Realmente por "debajo" no se bien q ocurre pero por lo que notado es mas confiable coloca siempre todas las condiciones de campos dentro del WHERE

[doylelives]

si pero a su vez dicha condicion me permite aplicar un filtro en el momento del join...
al menos eso interpreto..
sigo investigando.

[gnzsoloyo]

Brevemente:
Los DBMS en general tienen algoritmos internos que determinan el "camino" que usarán para obtener los resultados. Esos "caminos" no siempre son los mismos, incluso para consultas que involucren las mismas tablas porque además de utilizar la forma de escritura de la consulta, utilizan cierta inteligencia obtenida estadísticamente de los métodos utilizados que dieron mejor resultado previamente.
Ello se puede apreciar en algunos DBMS como SQL Server, que tiene una herramienta que permite visualizar el plan de ejecución de la consulta.
En el caso de MySQL pasa lo mismo, aunque sin la herramienta visual. De todos modos se cuenta con una sentencia denominada EXPLAIN la cual permite ver un análisis de lo que MySQL hace para obtener el resultado, y eventualemente las sugerencias para optimizar la misma.

Pero aún así, hay ciertas cosas que si podemos decir a priori:

1) Siempre es más eficiente usar JOIN (y especialmente INNER/LEFT/RIGHT) porque una gran parte del filtrado se produce mientras las tablas están siendo leídas, y también porque el FROM es optimizable por el parser, que resuelve diferentes modos de obtener el resultado.

2) Poner las condiciones en el WHERE es ineficiente, porque el parser no puede optimizarlo. Únicamente puede la performance ser afectada por la existencia de índices que trabajen sobre las condiciones puestas en él. Pero nada más. Pero un indice también puede provocar una baja performance, porque se trata de una estructura que hay que mantener... y todo objeto a mantener equivale a procesos corriendo en background...
Las condiciones del WHERE se aplican después de leer las tablas, por lo que es muy propenso a la creación de productos cartesianos, que es una de las peores condiciones de una consulta.

Finalmente: A niveles de pequeñas tablas (decenas de miles de registros), la performance de ambas cosas es similar. No pareciera haber demasiada diferencia, pero existe. Para ello es mejor utilizar el EXPLAIN.
Cuando la cantidad de registros llega a centenares de miles o millones, el efecto en la performance del JOIN se hace más evidente.
Piensa que por algo fue creado y por alguna razón es la forma más usada apra las consultas... y eso no es porque sea menos eficiente.

¿No te parece?

[doylelives]

gracias por la explicación, me gustaria saber interpretar y como mejorar la consulta a traves del uso de explain, pero no encontré algo asi, de casualidad conoces alguna pagina que lo explique con profundidad?