Re: Very slow query, help please

[Rick James]

If you are not ready to upgrade to the not-yet-released 5.6, then...

You have

select  proveidors_treballadors.id_prov_treb,
        nom, coalesce(fac,0)
    from  proveidors_treballadors
    left join  
      ( SELECT  id_prov_treb,
                ROUND((sum(materials+stock)), 2) as fac
            from  albarans
            left join  
              ( SELECT  id_albara, sum(quantitat*multiplicatiu) as materials
                    from  comandes_albarans_families
                    group by  id_albara) T1 on (albarans.id_albara=T1.id_albara)
            left join  
              ( SELECT  id_albara, sum(quantitat*multiplicatiu) as stock
                    from  albarans_families
                    group by  id_albara) T2 on (albarans.id_albara=T2.id_albara)
            group by  id_prov_treb) T3 on (proveidors_treballadors.id_prov_treb=T3.id_prov_treb)
    where  tipus_prov_treb = 'proveidor';

The main problem, as Øystein points out, is the lack of indexes on sub queries.

More specifically, this construct will perform poorly (until 5.6):
JOIN ( SELECT ... ) ON ...
JOIN ( SELECT ... ) ON ...
unless one or more of those subqueries returns only one row.

One approach is to explicitly create the "temporary" tables that are currently being created by the subquery, but add an index:

CREATE TEMPORARY TABLE T1 (
    PRIMARY KEY(id_albara)
) SELECT  id_albara,
        sum(quantitat*multiplicatiu) as materials
    from  comandes_albarans_families
    group by  id_albara;

(Similarly for T2)

Or you could upgrade to the latest 5.6.