Mejorar rendimiento SQL

[xorow]

Hola, he estado leyendo sobre la generación de consultas y el problema que me suele llevar cuando tengo mas de 100.000 elementos por tabla.

En primer lugar mes gustaría saber si en una consulta donde se contatenan 4 tablas por ejemplo, sería mejor el uso de LEFT JOIN o sin embargo usar condiciones dentro de la clausula WHERE como el siguiente ejemplo:

con where:

SELECT DISTINCT(p.id), p.nombre, p.url FROM provincia p, empresa e, empresa_subcategoria es, estancia_subcategoria esc WHERE p.id_pais='$pais' AND e.provincia=p.id AND e.id=es.id_empresa AND e.activado='1' AND esc.id_subcategoria=es.id_subcategoria AND esc.id_estancia='$id_estancia'

con left join:

SELECT DISTINCT(p.id), p.nombre, p.url
FROM provincia p
LEFT JOIN empresa e ON e.provincia=p.id
LEFT JOIN empresa_subcategoria es ON e.id=es.id_empresa
LEFT JOIN estancia_subcategoria esc ON esc.id_subcategoria=es.id_subcategoria
WHERE p.id_pais='$pais' AND e.activado='1' AND esc.id_estancia='$id_estancia'

Segun he leido lo más conveniente sería usar left join en algunas paginas, en otras dicen que es indiferente y en otras directamente que es peor el uso de left join. En cualquier caso me gustaría optimizar esta consulta al máximo.

Saludos y gracias

[Valery-Net]

El uso de Left, Right o Inner depende de las necesidades que tengas en tu consulta.

[xorow]

Hola Valery-Net, en este caso que expondo uso left join para que solo salgan los registros que no tienen null ya que son los que me interesant, solo me gustaría saber si en el caso que expongo sería mejor el uso de left join o simplemente poner todas las condiciones en el where

[gnzsoloyo]

Como te dice Valery-net, eso depende estrictamente de lo que necesites hacer.
LEFT JOIN y RIGHT JOIN hacen una junta apoyándose sobre la tabla izquierda y derecha, respectivamente. No son más o menos eficientes: depende de lo que desees lograr.
En general esos dos casos se usan principalmente cuando se sabe que puede haber falta de coincidencias entre ambas tablas y se desea operar incluso sobre los datos que no existe,
Un caso ejemplo podría ser averiguar cuántos pedidos se ha realizado a cada proveedor, tomando toda la lista de proveedores.

Código MySQL:

SELECT idProveedor, COUNT() pedidos
FROM proveedor LEFT JOIN pedido USING(idProveedor)
GROUP BY idProveedor;
 

Esto te devolvería todos los proveedores, poniendo cero (0) en la cantidad de pedidos en aquellos a los que no se les han realizado pedidos.

Código MySQL:

SELECT idProveedor, COUNT(*) pedidos
FROM proveedor RIGHT JOIN pedido USING(idProveedor)
GROUP BY idProveedor;
 

Esto solamente te mostraría sólo a aquellos aquellos a los que se les hizo pedidos.

Para los efectos de las consultas, en ciertas circunstancias, se puede requerir obtener los que no tienen registros relacionados:

Código MySQL:

SELECT v.idProveedor, v.nomProveedor
FROM proveedor V LEF JOIN pedido p USING(idProveedor)
WHERE P.idProveedor IS NULL;
 

Este modo solamente se puede usar con LEFT o RIGHT JOIN.

INNER JOIN hace que sólo devuelva las que tienen relación, sin importar si están a derecha o izquierda. Es mucho más eficiente porque el descarte no requiere lecturas independientes al parser para resolver la consulta.

Como te decíamos, depende de qué es lo que deseas hacer.
En el caso de tus ejemplos, el uso de INNER JOIN es lo que más corresponde, porque no estás realizando una tarea en que una tabla prevalezca sobre la otra.

[xorow]

Hola gnzsoloyo,

Lo que quiero por ejemplo en este ejemplo es que devuelva siempre las que tienen relacion, las que no tienen relacion no me interesan.

Como por ejemplo en este caso quiero mostrar las provincias que tengan empresas.

Código MySQL:

SELECT DISTINCT(p.id), p.nombre, p.url 
FROM provincia p 
LEFT JOIN empresa e ON e.provincia=p.id
WHERE e.activado='1''
 

me sugieres que use INNER JOIN para que descarte todas las provicinas que no tienen relacion con ninguna empresa?

[gnzsoloyo]

Exacto.
A menos que necesites recuperar otros registros, o basar la consulta en otros requerimientos, si lo que necesitas es una coincidencia absoluta, lo más simple es usar INNER JOIN.

Un detalle a recordar:
Técnicamente usar la coma o simplemente JOIN se supone que son sinónimos de INNER JOIN, pero esos dos casos requieren que solamente los campos que estén realmente relacionados tengan los mismos nombres en ambas tablas (caso en el cual puedes omitir ON ...). Hasta versiones anteriores a la 5.0.3 eran sintacticamente iguales, aunque ahora hay algunas diferencias de jerarquía.
El caso se volvía problemático si había en ambas tablas campos con igual nombre pero con diferente tipo de datos, un campo DESCRIPCION entre ItemFatura y ProductoStock, por ejemplo, pueden representar cosas distintas, pero el JOIN y la coma (,) los intentaban igualar, dando resultados nulos...
Por eso, precisamente, es más conveniente usar INNER JOIN, que te obliga a definir los campos de relación.

[xorow]

Muchas gracias a los dos,

No suelo usar el mismo nombre en los identificadores, ya que en cada tabla su ideantidicador le llamo id directamente, si luego tengo relacion con otra tabla le llamo id_(nombre_tabla).

Po último si se os ocurre sabriais en la consulta que he puesto mas simple si hay modo de desacerse de DISTINCT de algún modo, ya que existen mas de una empresa de una cierta provincia, si no lo pongo me sale la provincia repetida tantas veces como empresas de esa provincia hay

Código MySQL:

SELECT DISTINCT(p.id), p.nombre, p.url 
FROM provincia p 
INNER JOIN empresa e ON e.provincia=p.id
WHERE e.activado='1'
 

[gnzsoloyo]

No suelo usar el mismo nombre en los identificadores, ya que en cada tabla su ideantidicador le llamo id directamente, si luego tengo relacion con otra tabla le llamo id_(nombre_tabla).

Tip final:
En general se aconseja que los identificadores de la tabla no posean como nomber simplemente "ID". Por cuestiones mnemotécnicas es conveniente hacer uno más identificable, como el que usas como FK.
Eso facilita la escritura de consultas.
Si te fijas en las bases de modelo que te proveen los diferentes DBMS (Oracle, SQL Server, MySQL), verás que se usan de ese modo: id_nombretabla o bien nombretabla_id.
Eso permite escribir directamente, por ejemplo:

Código MySQL:

SELECT DISTINCT(p.id), p.nombre, p.url 
FROM provincia p INNER JOIN empresa e USING(id_provincia)
WHERE e.activado='1'
 

La cláusula USING requiere que el campo tenga el mismo nombre en ambas tablas, y es un poco más eficiente en la generación de los JOIN.

[xorow]

No lo sabia gnzsoloyo, muchas gracias.

Tendria algún modo de limitar que pare la consulta cuando encuentre una empresa de cada provincia, porque si tengo por ejemplo 50000 empresas de madrid, 20000 de valencia,.... La consulta sigue siendo grande.

[gnzsoloyo]

El paginado se puede resolver de dos modos:
1. Por programación, procesando toda la tabla devuelta. Eso es asunto del lenguaje y en definitiva puede recargar un poco las comunicaciones si los registros son muy largos, y la cantidad devuelta demasiado excesiva.
2. Usando la cláusula LIMIT offset, count, pero requiere la ejecución de una consulta diferente para cada página de resultados.

Código MySQL:

SELECT DISTINCT(p.id), p.nombre, p.url 
FROM provincia p INNER JOIN empresa e USING(id_provincia)
WHERE e.activado='1'
LIMIT 100;
 

o bien

Código MySQL:

SELECT DISTINCT(p.id), p.nombre, p.url 
FROM provincia p INNER JOIN empresa e USING(id_provincia)
WHERE e.activado='1'
LIMIT 0,100;
 

te devuelve lo mismo: los primeros 100 resultados.
Para obtener los siguientes 100 sería:

Código MySQL:

SELECT DISTINCT(p.id), p.nombre, p.url 
FROM provincia p INNER JOIN empresa e USING(id_provincia)
WHERE e.activado='1'
LIMIT 101, 100;
 

El problema es que en cada bloque deberías ir cambiando el offset, que representa desde cuál registro devolverá. Y creo que no se puede pasar como parámetro, sino que debe ser una constante. Pero hay que probar.