MySQL Forums
Forum List  »  Optimizer & Parser

Re: Very slow query, help please
Posted by: Rick James
Date: January 30, 2013 08:38AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
2651
January 29, 2013 10:18AM
1455
January 30, 2013 05:12AM
Re: Very slow query, help please
1405
January 30, 2013 08:38AM
1446
January 30, 2013 09:05AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.