duda con where e inner join.

[RIVERMILLOS]

hola tengo una duda sobre estas dos cosas , cuando hago una consulta con inner join junto con where, las condiciones del where solo se aplican a los datos que se filtre con inner join? osea digamos hay alguna diferencia en esto?

Código MySQL:

SELECT campo1,campo2 FROM tabla1
INNER JOIN tabla2
ON tabla1.id1=tabla2.id2 AND tabla2.otrocampo IN(1,2,4)
WHERE ......
 

Código MySQL:

SELECT campo1,campo2 FROM tabla1
INNER JOIN tabla2
ON tabla1.id1=tabla2.id2  
WHERE tabla2.otrocampo IN(1,2,4)
 

saludos

[gnzsoloyo]

Hay mucha diferencia en la implementación interna del INNER JOIN y el WHERE. La explicación es algo larga y bastante técnica. Ya la han hecho bastantes veces en el foro. Te sugeriría que uses el buscador y encuentres los posts que tratan de eso.
Si no los encuentras, entonces veremos de explicarlo de nuevo.

Por otro lado, en el contexto de tu ejemplo, en el primer caso ese WHERE es superfluo, a menos que tengas condiciones que no estás mostrando.

AL respecto del INNER JOIN te anticipo: El WHERE se aplica siempre sobre la tabla resultado del INNER JOIN.

[RIVERMILLOS]

Osea que es buena idea filtrar las cosas con inner join y asi en el where se aplique para pocos resultados? igual voy a buscar un poco mas, te comento mas o menos lo que yo hago , hago una consulta con inner join donde emparejo los ids de las tablas segun corresponda y al final tengo un where donde aplico ciertas condiciones lo que quiero es que cuando se apliquen las condiciones sea a pocos registro creo que asi mejoraria el performance de la consulta creo! XD

saludos

[gnzsoloyo]

El INNER JOIn te asegura que el bloque de datos a filtrar luego con WHERE cumple seguro con la restricción dada en el ON. Esa restricción además le asegura un prefiltrado eficiente y mucho más potente que lo que puedes lograr usando el WHERE.
Pero la eficiencia u optimización de una consulta son se cierra en ese punto. La selectividad de una relación entre dos tablas es crítica muchas veces.
Por ejemplo, si haces un INNER JOIN productos y ventas, obtendrás los productos efectivamente vendidos... pero eso en u supermercado puede representa millones de registros, mientras que si lo que quieres es un bloque menor, más restringido, puede requerirse, por ejemplo una subconsulta que genere una tabla derivada.
Por ejemplo:

Código MySQL:

SELECT 
    campo1, 
    campo2 
FROM 
    tabla1 T1 INNER JOIN 
    (SELECT * FROM tabla2 WHERE otrocampo IN(1,2,4)) T2 ON T1.id1= T2.id2 ;
WHERE [otras condiciones]
 

Te este modo, con esa subconsulta se puede restringir con qué datos vas a trabajar de una de las tablas, haciendo más eficiente la consulta.
Otra de las posibilidades es crear índices sobre determinados conjuntos de campos. Eso si, hay que ser cuidadoso con estos índices porque si tienes muchos INSERT/UPDATE, el exceso de índices puede afectar la performance de esos.

[RIVERMILLOS]

Esa consulta que colocaste , es a lo que llaman tablas temporales? porque he leido sobre eso en alguna parte creo que dicen que no es buena practica..


saludos

[gnzsoloyo]

No. Tablas temporales son cierto tipo de tablas como las TEMPORARY e incluso las MEMORY. Trabajan sobre otros motores de tablas.
Las tablas generadas por una subconsulta se denominan tablas derivadas y existen en todos los DBMS.
En cuanto a lo de que no sea una buena práctica, es como todo: Dependen del caso.
Tengo procedimientos almacenados que crean y destruyen más de diez tablas temporales en consultas extremadamente complejas, y no son ineficientes. Las pruebas realizadas con el mismo objetivo y con tablas fijas insumieron más tiempo de procesos, y fueron más lentas.
Por otro lado, en esos caso convinieron las temporales porque como existen en una conexión y son invisibles en las demás, no necesito definir nombres personalizados para crearlas. Uso los mismos y jamás hay conflictos.
Al respecto de las subconsultas en tablas derivadas, si tienes un caso con 15000 registros (por exagerar un poco), en una tabla relacionada con otra de 1000000, el INNER JOIN generará un conjunto de datos de al menos 1000000 de registros. Pero ¿qué pasa si de esos 15000 necesitas sólo 100? ¿Para qué generar una tabla esultado de 1.000.000 de registros para luego obtener sólo 100 ó 1000 de resultado final?
Además, como ter dije, esas cosas están muy afectadas por los índices. Tengo el caso de un procesamiento de alrededor de 647000 registros que tardaba con INNER JOIN sólo, más de 20 minutos en devolverme los resultados. Luego lo probé con una tabla derivada en los campos críticos y bajó a 17 minutos (es un caso de cantidades masivas de datos y campos espaciales).
Entonces probé ponerle un índice y dejar la subconsulta... Obtuve el mismo resultado en 18.6 segundos...

¿Entiendes la idea? No tomes las cosas al pie de la letra. Lo que resulta bueno para un caso es malo para otro y viceversa.

Cada caso puede requerir una solución diferenciada.

[RIVERMILLOS]

Muchas gracias por la respueta tan buena , lo que sucede es que tengo unas tablas con demaciados registros mas o menos estan entre 800000 registro donde manejo datos espaciales entonces estoy paranoico con las consultas a estas tablas , mira la consulta real que utilizo

Código MySQL:

SELECT         P.idproperty,AsText(P.datapoli) poligono,TP.typeproperty,PXU.idufb,P.center
               FROM propertys P
               INNER JOIN typepropertys TP
               ON TP.idtypeproperty=P.idtypeproperty
               LEFT JOIN propertyxusers PXU
               ON  PXU.idpropiedad=P.idproperty
               INNER JOIN blocks M
               ON M.idblock = P.idblock
               INNER JOIN Ne B
               ON B.idNe=M.idNe
               WHERE (PXU.idufb!='".$this->idufb."' OR  PXU.idufb IS NULL)
               AND   B.idNe IN(".$ids.")
               AND   (Contains  (GeomFromText ('".$polygon."'),P.datapoli) OR
                      Intersects(GeomFromText ('".$polygon."'),P.datapoli))
 

la consulta en este momento esta respondiendo bien no es lenta , pero me asusta cuando cresca el numero de usuarios que pueda falla , no si esta consulta como tal esta bn construida o se puede mejorar ,la tabla que tiene 800000 es propertys y Ne tiene 1608 registro.


saludos