Consulta muy larga

[Heent]

Buenas gente, tengo unas tablas con unos cuantos registros (foto e imatges tienen aproximadamente 100k registros cada una mientras que familia y autor no llegan a los 3k). La cosa es que hay información interrelacionada entre las distintas tablas que es:

Cada foto tiene una familia, un autor y una imagen. Están relacionados con un id, es decir cada autor y familia tienen un identificador que es el que está guardado a la tabla de foto.

Entonces me gustaría recuperar todas las fotos de un pueblo determinado (cada foto procede de un pueblo) con la información completa de cada autor y familia también. Por lo que estoy viendo en el servidor mi consulta es muy poco optima porqué satura toda mi CPU y tarda un montón.

Código MySQL:

SELECT f.des, f.referencia, f.familia AS num, f.fons, 
               a.nombre AS autor, fa.nombreMostrar AS familia, img.url
 
        FROM foto f
 
        LEFT JOIN ( SELECT a.nombre, a.id
                    FROM autor a
                    GROUP BY a.id ) a ON f.autor=a.id 
 
        LEFT JOIN ( SELECT fa.nombreMostrar, fa.numero, fa.id
                    FROM familia fa
                    WHERE fa.pueblo='$pueblo'
                    GROUP BY fa.id ) fa ON f.familia=fa.numero 
 
        LEFT JOIN ( SELECT img.url, img.referencia
                    FROM imatges img
                    GROUP BY img.referencia ) img ON f.referencia=img.referencia 
 
        WHERE pueblo='$pueblo' ORDER BY f.referencia;
 

¿Como podría optimizar la consulta?


Un slaudo y muchas gracias!

[quimfv]

Código MySQL:

SELECT f.des, f.referencia, f.familia AS num, f.fons, 
       a.nombre AS autor, fa.nombreMostrar AS familia, img.url
FROM foto f
        LEFT JOIN autor a ON f.autor=a.id 
        LEFT JOIN familia fa ON f.familia=fa.numero 
        LEFT JOIN imatges img ON f.referencia=img.referencia 
WHERE fa.pueblo='$pueblo' 
ORDER BY f.referencia;
 

No acabas de dar la información necesaria para entender porque hacias esos GROUP BY pero si los datos estan bien no serian necesarios.

Tambien se podría simplificar quitando los LEFT JOIN y subtituyendolos por INNER JOIN pero para ello debería saber si hay o no fotos sin autor, familia, o imatges, este último casi que no tendría sentido una foto sense imatge, no?

[Heent]

Buenas, muchas gracias. En principio puede darse el caso de una foto sin familia pero muy raramente.

Entonces es más eficiente un INNER JOIN que un LEFT JOIN y un LEFT JOIN es mejor que un LEFT JOIN con SELECT, no?


Un saludo y muchas gracias!

[gnzsoloyo]

No exactamente.
Un INNER JOIN no se usa en los mismos casos que los LEFT y RIGHT. Depende de lo que se desea obtener.
El INNER JOIN requiere que haya relación ion entre ambas tablas, y devolverá sólo los registros coincidentes.
El LEFT JOIN devolverá todo lo de la primera tabla, incluso si no hay coincidencia con la segunda, poniendo nulo en los campos devueltos de la segunda.