MySql query optimization

Hi!

This is a simplified example of a much more complex multi-sum queries that I have to perform on a very large database witch thousand of records:

select t1.id as cid,
ti.name,
SUM1.sum,
SUM2.sum,
SUM3.sum
from t1,

(
 select t1.id as cid,
 coalesce(sum(t2.value),0.00) as "sum"
 from
 t1,t2
 where
 t1.id=t2.cust_id
 group by cid
) SUM1,

(

...
) SUM2,

(


) SUM3

where
cid=SUM1.cid
and
cid=SUM2.cid
...

Is there a way to optimize such queries? It is always necessary to perform all the subqueries in a such way on a large database? I look for a more optimized solution...

Many thanks in advance


mlattariAsked:
Who is Participating?
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
I would use the method that I posted to move all your subqueries to joins.  That may help.
0
 
cyberkiwiCommented:
Without looking at the full query, it is going to be speculation.
You have not indicated whether SUM2 or SUM3 are even of the same form as SUM1.
If there are WHERE clauses that restrict the records from t1 at the outermost main query, then it would sometimes make sense to move the sums into the SELECT portion.  Or even duplicating the where (against t1) into SUM1's subquery.
0
 
johnsoneSenior Oracle DBACommented:
Without the full query, we don't really know what is going on, but is this what you are trying to accomplish?
select t1.id as cid, 
       t1.name, 
       sum(t2.value)
  from t1, t2
 where t1.id = t2.cid(+)
 group by t1.id, t1.name

Open in new window

0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
johnsoneSenior Oracle DBACommented:
Given the different databases that you supplied, this is probably a better syntax, as the previous would only work in Oracle.

select t1.id as cid, 
       t1.name, 
       sum(t2.value)
  from t1 left join t2 on t1.id = t2.cid
 group by t1.id, t1.name

Open in new window

0
 
mlattariAuthor Commented:
This is the full query :-)))))))))))))))))))

select
ordinazioni.id as IDORD,
concat(\"<b>\",left(ordinazioni.titolo,25),\"</b>\",\"<br>Wartosc: &#8364; \",V.w,\"<br>Zaplacono: &#8364; \",ZAPLACONO.suma) as \"Nazwa\",
coalesce(concat(\"[\",transporty.id,\"] <b>\",transporty.nazwa,\"</b> (\",statusy_transportu.status,\")\",\"<br>Ladunek: \",ZALADOWANO.Kg, \" / \",transporty.ladownosc_kg,\"Kg.\",\"<br>Stolarka okienna: \",ZALADOWANO_OKIEN.qnt,\" szt.<br>Okiennice aluminiowe: \",ZALADOWANO_OKIENNIC.qnt,\" szt.<br>Suma oplat: &#8364; \",OPLATA_TRANSPORT.suma),\"Brak transportu\") as \"Transport\",
ordinazioni.cap as \"C.A.P.\",
left(ordinazioni.miasto_dostawy,25) as \"Dostawa do\",
ordinazioni.koszt_transportu as \"Koszt spedycji\",
if (to_days(max(posizioni.data_merce_a_magazzino))-to_days(curdate())<0, \"gotowe\", concat( \"za \", to_days(max(posizioni.data_merce_a_magazzino))-to_days(curdate()), \" dni\" ) ) as \"Gotowe\",

concat(SPCV.qnt,\" szt. [\",SPCV.m2,\"m2,\", SPCV.Kg, \" Kg.] \") as \"Stolarka okienna\",
concat(OALU.qnt,\" szt. [\",OALU.m2,\"m2,\", OALU.Kg, \" Kg.] \") as \"Okiennice Alum.\",

coalesce(transporty.status,0) as stat,
coalesce(kolory.kolor,0) as kolor,
coalesce(max(posizioni.data_merce_a_magazzino),\"0000-00-00\") as \"data_gotowosci\"


from
ordinazioni
left join posizioni on ordinazioni.id=posizioni.id_ordinazione
left join transporty on (ordinazioni.id_transportu=transporty.id)

left join
(  select
   transporty.id as transid,
   coalesce(round(sum(specyfikacja.width*specyfikacja.height*specyfikacja.ilosc*0.000001*asortyment.waga_m2),2),0.00) as Kg
   from
   transporty,
   ordinazioni
   left join posizioni on (posizioni.id_ordinazione=ordinazioni.id)
   left join asortyment on (posizioni.asortyment=asortyment.id)
   left join specyfikacja on (posizioni.id=specyfikacja.posizioni_id)
   where
   ordinazioni.id_transportu=transporty.id
   and transporty.status<>4
   and ordinazioni.id>700
   group by transid  
  ) ZALADOWANO on (ordinazioni.id_transportu=ZALADOWANO.transid)

 left join
(  select
   transporty.id as transid,
   coalesce(sum(specyfikacja.ilosc),0) as qnt
   from
   transporty,
   ordinazioni
   left join posizioni on (posizioni.id_ordinazione=ordinazioni.id and posizioni.asortyment=6)
   left join asortyment on (posizioni.asortyment=asortyment.id)
   left join specyfikacja on (posizioni.id=specyfikacja.posizioni_id)
   where
   ordinazioni.id_transportu=transporty.id
   and transporty.status<>4
   and ordinazioni.id>700
   group by transid  
  ) ZALADOWANO_OKIEN on (ordinazioni.id_transportu=ZALADOWANO_OKIEN.transid)
 
  left join
(  select
   transporty.id as transid,
   coalesce(sum(specyfikacja.ilosc),0) as qnt
   from
   transporty,
   ordinazioni
   left join posizioni on (posizioni.id_ordinazione=ordinazioni.id and posizioni.asortyment=7)
   left join asortyment on (posizioni.asortyment=asortyment.id)
   left join specyfikacja on (posizioni.id=specyfikacja.posizioni_id)
   where
   ordinazioni.id_transportu=transporty.id
   and transporty.status<>4
   and ordinazioni.id>700
   group by transid  
  ) ZALADOWANO_OKIENNIC on (ordinazioni.id_transportu=ZALADOWANO_OKIENNIC.transid)
 
 
left join statusy_transportu on (transporty.status=statusy_transportu.id)
left join kolory on (transporty.kolor=kolory.id)

left join
(
 select
 ordinazioni.id_transportu as transid,
 sum(ordinazioni.koszt_transportu) as suma
 from
 ordinazioni,transporty
 where
 ordinazioni.id_transportu=transporty.id
 and
 ordinazioni.id_transportu<>0
 and
 transporty.status<>4
 group by
 transid
) OPLATA_TRANSPORT on (ordinazioni.id_transportu=OPLATA_TRANSPORT.transid)


,


( select
  ordinazioni.id as \"idord\",
  coalesce(sum(posizioni.valore),0.00) as \"w\"
  from
  ordinazioni
  left join posizioni on ordinazioni.id=posizioni.id_ordinazione
  where ordinazioni.id>700
  group by \"idord\"
 ) V,
 
 (
  select ordinazioni.id as \"idord\",
  coalesce(sum(specyfikacja.ilosc),0) as qnt,
  coalesce(round(sum(specyfikacja.width*specyfikacja.height*specyfikacja.ilosc*0.000001),2),0.00) as m2,
  coalesce(round(sum(specyfikacja.width*specyfikacja.height*specyfikacja.ilosc*0.000001)*asortyment.waga_m2,2),0.00) as Kg
  from
  ordinazioni
  left join posizioni on (ordinazioni.id=posizioni.id_ordinazione and posizioni.asortyment=6)
  left join specyfikacja on (posizioni.id=specyfikacja.posizioni_id)
  left join asortyment on (posizioni.asortyment=asortyment.id )
  where ordinazioni.id>700
  group by \"idord\"
  ) SPCV,

  (
  select ordinazioni.id as \"idord\",
  coalesce(sum(specyfikacja.ilosc),0) as qnt,
  coalesce(round(sum(specyfikacja.width*specyfikacja.height*specyfikacja.ilosc*0.000001),2),0.00) as m2,
  coalesce(round(sum(specyfikacja.width*specyfikacja.height*specyfikacja.ilosc*0.000001)*asortyment.waga_m2,2),0.00) as Kg
  from
  ordinazioni
  left join posizioni on (ordinazioni.id=posizioni.id_ordinazione and posizioni.asortyment=7)
  left join specyfikacja on (posizioni.id=specyfikacja.posizioni_id)
  left join asortyment on (posizioni.asortyment=asortyment.id )
  where ordinazioni.id>700
  group by \"idord\"
  ) OALU,
 
  (
   select
   ordinazioni.id as idord,
   coalesce(sum(pagamenti.importo),0.00) as suma
   from
   ordinazioni
   left join ordinazioni_pagamenti on (ordinazioni.id=ordinazioni_pagamenti.id_ordinazione)
   left join pagamenti on (ordinazioni_pagamenti.id_pagamento=pagamenti.id)
   where
   ordinazioni.id>700
   group by idord
  ) ZAPLACONO
 
 
     
where
ordinazioni.id=V.idord
and
ordinazioni.id=SPCV.idord
and
ordinazioni.id=OALU.idord
and
ordinazioni.id=ZAPLACONO.idord
and
ordinazioni.id>700
group by ordinazioni.id
having ((to_days(max(posizioni.data_merce_a_magazzino))-to_days(curdate())) < 14) and stat<>4
order by `C.A.P.` asc;
0
 
mlattariAuthor Commented:
Hmmm nobody knows? :-(
0
 
mlattariAuthor Commented:
This version works fine but I look for a way to optimize the query because of the intensive subqueries on thousands of records. Any ideas? Thanks in advance.


$transporty=mysql_query("

select
ordinazioni.id as IDORD,
concat(\"<b>\",left(ordinazioni.titolo,25),\"</b>\",\"<br>Wartosc: &#8364; \",V.w,\"<br>Zaplacono: &#8364; \",ZAPLACONO.suma) as \"Nazwa\",
coalesce(concat(\"[\",transporty.id,\"] <b>\",transporty.nazwa,\"</b> (\",statusy_transportu.status,\")\",\"<br>Ladunek: \",ZALADOWANO.Kg, \" / \",transporty.ladownosc_kg,\"Kg.\",\"<br>Stolarka okienna: \",ZALADOWANO_OKIEN.qnt,\" szt.<br>Okiennice aluminiowe: \",ZALADOWANO_OKIENNIC.qnt,\" szt.<br>Suma oplat: &#8364; \",OPLATA_TRANSPORT.suma),\"Brak transportu\") as \"Transport\",
ordinazioni.cap as \"C.A.P.\",
left(ordinazioni.miasto_dostawy,25) as \"Dostawa do\",
ordinazioni.koszt_transportu as \"Koszt spedycji\",
if (to_days(max(posizioni.data_merce_a_magazzino))-to_days(curdate())<0, \"gotowe\", concat( \"za \", to_days(max(posizioni.data_merce_a_magazzino))-to_days(curdate()), \" dni\" ) ) as \"Gotowe\",

concat(SPCV.qnt,\" szt. [\",SPCV.m2,\"m2,\", SPCV.Kg, \" Kg.] \") as \"Stolarka okienna\",
concat(OALU.qnt,\" szt. [\",OALU.m2,\"m2,\", OALU.Kg, \" Kg.] \") as \"Okiennice Alum.\",

coalesce(transporty.status,0) as stat,
coalesce(kolory.kolor,0) as kolor,
coalesce(max(posizioni.data_merce_a_magazzino),\"0000-00-00\") as \"data_gotowosci\"

from
ordinazioni
left join posizioni on ordinazioni.id=posizioni.id_ordinazione
left join transporty on (ordinazioni.id_transportu=transporty.id)

left join
(  select
   transporty.id as transid,
   coalesce(round(sum(specyfikacja.width*specyfikacja.height*specyfikacja.ilosc*0.000001*asortyment.waga_m2),2),0.00) as Kg
   from
   transporty,
   ordinazioni
   left join posizioni on (posizioni.id_ordinazione=ordinazioni.id)
   left join asortyment on (posizioni.asortyment=asortyment.id)
   left join specyfikacja on (posizioni.id=specyfikacja.posizioni_id)
   where
   ordinazioni.id_transportu=transporty.id
   and transporty.status<>4
   and ordinazioni.id>700
   group by transid  
  ) ZALADOWANO on (ordinazioni.id_transportu=ZALADOWANO.transid)

 left join
(  select
   transporty.id as transid,
   coalesce(sum(specyfikacja.ilosc),0) as qnt
   from
   transporty,
   ordinazioni
   left join posizioni on (posizioni.id_ordinazione=ordinazioni.id and posizioni.asortyment=6)
   left join asortyment on (posizioni.asortyment=asortyment.id)
   left join specyfikacja on (posizioni.id=specyfikacja.posizioni_id)
   where
   ordinazioni.id_transportu=transporty.id
   and transporty.status<>4
   and ordinazioni.id>700
   group by transid  
  ) ZALADOWANO_OKIEN on (ordinazioni.id_transportu=ZALADOWANO_OKIEN.transid)
 
  left join
(  select
   transporty.id as transid,
   coalesce(sum(specyfikacja.ilosc),0) as qnt
   from
   transporty,
   ordinazioni
   left join posizioni on (posizioni.id_ordinazione=ordinazioni.id and posizioni.asortyment=7)
   left join asortyment on (posizioni.asortyment=asortyment.id)
   left join specyfikacja on (posizioni.id=specyfikacja.posizioni_id)
   where
   ordinazioni.id_transportu=transporty.id
   and transporty.status<>4
   and ordinazioni.id>700
   group by transid  
  ) ZALADOWANO_OKIENNIC on (ordinazioni.id_transportu=ZALADOWANO_OKIENNIC.transid)
 
left join statusy_transportu on (transporty.status=statusy_transportu.id)
left join kolory on (transporty.kolor=kolory.id)

left join
(
 select
 ordinazioni.id_transportu as transid,
 sum(ordinazioni.koszt_transportu) as suma
 from
 ordinazioni,transporty
 where
 ordinazioni.id_transportu=transporty.id
 and
 ordinazioni.id_transportu<>0
 and
 transporty.status<>4
 and
 ordinazioni.id>700
 group by
 transid
) OPLATA_TRANSPORT on (ordinazioni.id_transportu=OPLATA_TRANSPORT.transid)

left join
( select
  ordinazioni.id as idord,
  coalesce(sum(posizioni.valore),0.00) as w
  from
  ordinazioni
  left join posizioni on ordinazioni.id=posizioni.id_ordinazione
  where ordinazioni.id>700
  group by idord
 ) V on ordinazioni.id=V.idord
 
left join
(
  select ordinazioni.id as idord,
  coalesce(sum(specyfikacja.ilosc),0) as qnt,
  coalesce(round(sum(specyfikacja.width*specyfikacja.height*specyfikacja.ilosc*0.000001),2),0.00) as m2,
  coalesce(round(sum(specyfikacja.width*specyfikacja.height*specyfikacja.ilosc*0.000001)*asortyment.waga_m2,2),0.00) as Kg
  from
  ordinazioni
  left join posizioni on (ordinazioni.id=posizioni.id_ordinazione and posizioni.asortyment=6)
  left join specyfikacja on (posizioni.id=specyfikacja.posizioni_id)
  left join asortyment on (posizioni.asortyment=asortyment.id )
  where ordinazioni.id>700
  group by idord
  ) SPCV on ordinazioni.id=SPCV.idord

 left join
 (
  select ordinazioni.id as idord,
  coalesce(sum(specyfikacja.ilosc),0) as qnt,
  coalesce(round(sum(specyfikacja.width*specyfikacja.height*specyfikacja.ilosc*0.000001),2),0.00) as m2,
  coalesce(round(sum(specyfikacja.width*specyfikacja.height*specyfikacja.ilosc*0.000001)*asortyment.waga_m2,2),0.00) as Kg
  from
  ordinazioni
  left join posizioni on (ordinazioni.id=posizioni.id_ordinazione and posizioni.asortyment=7)
  left join specyfikacja on (posizioni.id=specyfikacja.posizioni_id)
  left join asortyment on (posizioni.asortyment=asortyment.id )
  where ordinazioni.id>700
  group by idord
  ) OALU on ordinazioni.id=OALU.idord
 
 left join
  (
   select
   ordinazioni.id as idord,
   coalesce(sum(pagamenti.importo),0.00) as suma
   from
   ordinazioni
   left join ordinazioni_pagamenti on (ordinazioni.id=ordinazioni_pagamenti.id_ordinazione)
   left join pagamenti on (ordinazioni_pagamenti.id_pagamento=pagamenti.id)
   where
   ordinazioni.id>700
   group by idord
  ) ZAPLACONO on ordinazioni.id=ZAPLACONO.idord
 
where ordinazioni.id>700
group by ordinazioni.id    
having ((to_days(max(posizioni.data_merce_a_magazzino))-to_days(curdate())) < 14) and stat<>4
order by `C.A.P.` asc;

") or show_erx(1);
0
 
mlattariAuthor Commented:

The problem is that every subquery of the presented query selects thousands of records to match a single row (for ewery row) of the main query.
Is there a more sophisticated solution?
0
 
mlattariAuthor Commented:
No ideas? Nobody can help me?
0
 
mlattariAuthor Commented:
Nobody knows?
0
 
mlattariAuthor Commented:
Why nobody can help me?
0
 
mlattariAuthor Commented:
johnsone:
"I would use the method that I posted to move all your subqueries to joins.  That may help. "

How ? The subqueries are now all left joins. How to do multiple SUMS without left join subqueries? The main query contains MAX and the subqueries contain SUMS. The whole query, with thousands of matched records by the subqueries, is very slow...
0
 
mlattariAuthor Commented:
Is there a way to optimize the query? Nobody knows?
0
 
mlattariAuthor Commented:
Hi!

You helped me in some way :-)  but I must put a new question now...
0
 
johnsoneSenior Oracle DBACommented:
You want to remove the subqueries completely and make join to the tables in the outer query.  This should eliminate the need to fully materialize the subquery before being able to do the joins.  Only the part of the subquery that is required in the outer query will need to be retrieved.

This:

select t1.id as cid,
       t1.name,
       value
  from t1 left join (select cid, sum(value) value from t2 group by id) c on t1.id = c.cid
 group by t1.id, t1.name

becomes this:

select t1.id as cid,
       t1.name,
       sum(t2.value)
  from t1 left join t2 on t1.id = t2.cid
 group by t1.id, t1.name
0
 
mlattariAuthor Commented:
Hi Johnsone!

Thanks for the help but this solution does not work with multiple sums...
0
 
johnsoneSenior Oracle DBACommented:
It should because at the outermost query, you are still grouping by only 1 set of criteria.
0
 
mlattariAuthor Commented:
hmmm I will try :-) and I let you know ...
0
 
mlattariAuthor Commented:
Hi johnsone!

I have tried to do the sums without subqueries but the sums are then incorrect, mostly doubled.
This does not work without subqueries:

mysql> select
            ordinazioni.id,
            sum(pagamenti.importo),
            sum(posizioni.valore)
            from
            ordinazioni
            left join posizioni on (ordinazioni.id=posizioni.id_ordinazione)
            left join ordinazioni_pagamenti on ordinazioni.id=ordinazioni_pagamenti.id_ordinazione
           left join pagamenti on ordinazioni_pagamenti.id_pagamento=pagamenti.id
           group by ordinazioni.id;

The resulting sums are WRONG
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.