mlattari
asked on
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.0 0) 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
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.0
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
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
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
ASKER
This is the full query :-)))))))))))))))))))
select
ordinazioni.id as IDORD,
concat(\"<b>\",left(ordina zioni.tito lo,25),\"< /b>\",\"<b r>Wartosc: € \",V.w,\"<br>Zaplacono: € \",ZAPLACONO.suma) as \"Nazwa\",
coalesce(concat(\"[\",tran sporty.id, \"] <b>\",transporty.nazwa,\"< /b> (\",statusy_transportu.sta tus,\")\", \"<br>Ladu nek: \",ZALADOWANO.Kg, \" / \",transporty.ladownosc_kg ,\"Kg.\",\ "<br>Stola rka okienna: \",ZALADOWANO_OKIEN.qnt,\" szt.<br>Okiennice aluminiowe: \",ZALADOWANO_OKIENNIC.qnt ,\" szt.<br>Suma oplat: € \",OPLATA_TRANSPORT.suma), \"Brak transportu\") as \"Transport\",
ordinazioni.cap as \"C.A.P.\",
left(ordinazioni.miasto_do stawy,25) as \"Dostawa do\",
ordinazioni.koszt_transpor tu as \"Koszt spedycji\",
if (to_days(max(posizioni.dat a_merce_a_ magazzino) )-to_days( curdate()) <0, \"gotowe\", concat( \"za \", to_days(max(posizioni.data _merce_a_m agazzino)) -to_days(c urdate()), \" 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.dat a_merce_a_ magazzino) ,\"0000-00 -00\") as \"data_gotowosci\"
from
ordinazioni
left join posizioni on ordinazioni.id=posizioni.i d_ordinazi one
left join transporty on (ordinazioni.id_transportu =transport y.id)
left join
( select
transporty.id as transid,
coalesce(round(sum(specyfi kacja.widt h*specyfik acja.heigh t*specyfik acja.ilosc *0.000001* asortyment .waga_m2), 2),0.00) as Kg
from
transporty,
ordinazioni
left join posizioni on (posizioni.id_ordinazione= ordinazion i.id)
left join asortyment on (posizioni.asortyment=asor tyment.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 =ZALADOWAN O.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= ordinazion i.id and posizioni.asortyment=6)
left join asortyment on (posizioni.asortyment=asor tyment.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 =ZALADOWAN O_OKIEN.tr ansid)
left join
( select
transporty.id as transid,
coalesce(sum(specyfikacja. ilosc),0) as qnt
from
transporty,
ordinazioni
left join posizioni on (posizioni.id_ordinazione= ordinazion i.id and posizioni.asortyment=7)
left join asortyment on (posizioni.asortyment=asor tyment.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 =ZALADOWAN O_OKIENNIC .transid)
left join statusy_transportu on (transporty.status=statusy _transport u.id)
left join kolory on (transporty.kolor=kolory.i d)
left join
(
select
ordinazioni.id_transportu as transid,
sum(ordinazioni.koszt_tran sportu) 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_TR ANSPORT.tr ansid)
,
( select
ordinazioni.id as \"idord\",
coalesce(sum(posizioni.val ore),0.00) as \"w\"
from
ordinazioni
left join posizioni on ordinazioni.id=posizioni.i d_ordinazi one
where ordinazioni.id>700
group by \"idord\"
) V,
(
select ordinazioni.id as \"idord\",
coalesce(sum(specyfikacja. ilosc),0) as qnt,
coalesce(round(sum(specyfi kacja.widt h*specyfik acja.heigh t*specyfik acja.ilosc *0.000001) ,2),0.00) as m2,
coalesce(round(sum(specyfi kacja.widt h*specyfik acja.heigh t*specyfik acja.ilosc *0.000001) *asortymen t.waga_m2, 2),0.00) as Kg
from
ordinazioni
left join posizioni on (ordinazioni.id=posizioni. id_ordinaz ione and posizioni.asortyment=6)
left join specyfikacja on (posizioni.id=specyfikacja .posizioni _id)
left join asortyment on (posizioni.asortyment=asor tyment.id )
where ordinazioni.id>700
group by \"idord\"
) SPCV,
(
select ordinazioni.id as \"idord\",
coalesce(sum(specyfikacja. ilosc),0) as qnt,
coalesce(round(sum(specyfi kacja.widt h*specyfik acja.heigh t*specyfik acja.ilosc *0.000001) ,2),0.00) as m2,
coalesce(round(sum(specyfi kacja.widt h*specyfik acja.heigh t*specyfik acja.ilosc *0.000001) *asortymen t.waga_m2, 2),0.00) as Kg
from
ordinazioni
left join posizioni on (ordinazioni.id=posizioni. id_ordinaz ione and posizioni.asortyment=7)
left join specyfikacja on (posizioni.id=specyfikacja .posizioni _id)
left join asortyment on (posizioni.asortyment=asor tyment.id )
where ordinazioni.id>700
group by \"idord\"
) OALU,
(
select
ordinazioni.id as idord,
coalesce(sum(pagamenti.imp orto),0.00 ) as suma
from
ordinazioni
left join ordinazioni_pagamenti on (ordinazioni.id=ordinazion i_pagament i.id_ordin azione)
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.i dord
and
ordinazioni.id>700
group by ordinazioni.id
having ((to_days(max(posizioni.da ta_merce_a _magazzino ))-to_days (curdate() )) < 14) and stat<>4
order by `C.A.P.` asc;
select
ordinazioni.id as IDORD,
concat(\"<b>\",left(ordina
coalesce(concat(\"[\",tran
ordinazioni.cap as \"C.A.P.\",
left(ordinazioni.miasto_do
ordinazioni.koszt_transpor
if (to_days(max(posizioni.dat
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
coalesce(kolory.kolor,0) as kolor,
coalesce(max(posizioni.dat
from
ordinazioni
left join posizioni on ordinazioni.id=posizioni.i
left join transporty on (ordinazioni.id_transportu
left join
( select
transporty.id as transid,
coalesce(round(sum(specyfi
from
transporty,
ordinazioni
left join posizioni on (posizioni.id_ordinazione=
left join asortyment on (posizioni.asortyment=asor
left join specyfikacja on (posizioni.id=specyfikacja
where
ordinazioni.id_transportu=
and transporty.status<>4
and ordinazioni.id>700
group by transid
) ZALADOWANO on (ordinazioni.id_transportu
left join
( select
transporty.id as transid,
coalesce(sum(specyfikacja.
from
transporty,
ordinazioni
left join posizioni on (posizioni.id_ordinazione=
left join asortyment on (posizioni.asortyment=asor
left join specyfikacja on (posizioni.id=specyfikacja
where
ordinazioni.id_transportu=
and transporty.status<>4
and ordinazioni.id>700
group by transid
) ZALADOWANO_OKIEN on (ordinazioni.id_transportu
left join
( select
transporty.id as transid,
coalesce(sum(specyfikacja.
from
transporty,
ordinazioni
left join posizioni on (posizioni.id_ordinazione=
left join asortyment on (posizioni.asortyment=asor
left join specyfikacja on (posizioni.id=specyfikacja
where
ordinazioni.id_transportu=
and transporty.status<>4
and ordinazioni.id>700
group by transid
) ZALADOWANO_OKIENNIC on (ordinazioni.id_transportu
left join statusy_transportu on (transporty.status=statusy
left join kolory on (transporty.kolor=kolory.i
left join
(
select
ordinazioni.id_transportu as transid,
sum(ordinazioni.koszt_tran
from
ordinazioni,transporty
where
ordinazioni.id_transportu=
and
ordinazioni.id_transportu<
and
transporty.status<>4
group by
transid
) OPLATA_TRANSPORT on (ordinazioni.id_transportu
,
( select
ordinazioni.id as \"idord\",
coalesce(sum(posizioni.val
from
ordinazioni
left join posizioni on ordinazioni.id=posizioni.i
where ordinazioni.id>700
group by \"idord\"
) V,
(
select ordinazioni.id as \"idord\",
coalesce(sum(specyfikacja.
coalesce(round(sum(specyfi
coalesce(round(sum(specyfi
from
ordinazioni
left join posizioni on (ordinazioni.id=posizioni.
left join specyfikacja on (posizioni.id=specyfikacja
left join asortyment on (posizioni.asortyment=asor
where ordinazioni.id>700
group by \"idord\"
) SPCV,
(
select ordinazioni.id as \"idord\",
coalesce(sum(specyfikacja.
coalesce(round(sum(specyfi
coalesce(round(sum(specyfi
from
ordinazioni
left join posizioni on (ordinazioni.id=posizioni.
left join specyfikacja on (posizioni.id=specyfikacja
left join asortyment on (posizioni.asortyment=asor
where ordinazioni.id>700
group by \"idord\"
) OALU,
(
select
ordinazioni.id as idord,
coalesce(sum(pagamenti.imp
from
ordinazioni
left join ordinazioni_pagamenti on (ordinazioni.id=ordinazion
left join pagamenti on (ordinazioni_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.i
and
ordinazioni.id>700
group by ordinazioni.id
having ((to_days(max(posizioni.da
order by `C.A.P.` asc;
ASKER
Hmmm nobody knows? :-(
ASKER
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(ordina zioni.tito lo,25),\"< /b>\",\"<b r>Wartosc: € \",V.w,\"<br>Zaplacono: € \",ZAPLACONO.suma) as \"Nazwa\",
coalesce(concat(\"[\",tran sporty.id, \"] <b>\",transporty.nazwa,\"< /b> (\",statusy_transportu.sta tus,\")\", \"<br>Ladu nek: \",ZALADOWANO.Kg, \" / \",transporty.ladownosc_kg ,\"Kg.\",\ "<br>Stola rka okienna: \",ZALADOWANO_OKIEN.qnt,\" szt.<br>Okiennice aluminiowe: \",ZALADOWANO_OKIENNIC.qnt ,\" szt.<br>Suma oplat: € \",OPLATA_TRANSPORT.suma), \"Brak transportu\") as \"Transport\",
ordinazioni.cap as \"C.A.P.\",
left(ordinazioni.miasto_do stawy,25) as \"Dostawa do\",
ordinazioni.koszt_transpor tu as \"Koszt spedycji\",
if (to_days(max(posizioni.dat a_merce_a_ magazzino) )-to_days( curdate()) <0, \"gotowe\", concat( \"za \", to_days(max(posizioni.data _merce_a_m agazzino)) -to_days(c urdate()), \" 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.dat a_merce_a_ magazzino) ,\"0000-00 -00\") as \"data_gotowosci\"
from
ordinazioni
left join posizioni on ordinazioni.id=posizioni.i d_ordinazi one
left join transporty on (ordinazioni.id_transportu =transport y.id)
left join
( select
transporty.id as transid,
coalesce(round(sum(specyfi kacja.widt h*specyfik acja.heigh t*specyfik acja.ilosc *0.000001* asortyment .waga_m2), 2),0.00) as Kg
from
transporty,
ordinazioni
left join posizioni on (posizioni.id_ordinazione= ordinazion i.id)
left join asortyment on (posizioni.asortyment=asor tyment.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 =ZALADOWAN O.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= ordinazion i.id and posizioni.asortyment=6)
left join asortyment on (posizioni.asortyment=asor tyment.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 =ZALADOWAN O_OKIEN.tr ansid)
left join
( select
transporty.id as transid,
coalesce(sum(specyfikacja. ilosc),0) as qnt
from
transporty,
ordinazioni
left join posizioni on (posizioni.id_ordinazione= ordinazion i.id and posizioni.asortyment=7)
left join asortyment on (posizioni.asortyment=asor tyment.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 =ZALADOWAN O_OKIENNIC .transid)
left join statusy_transportu on (transporty.status=statusy _transport u.id)
left join kolory on (transporty.kolor=kolory.i d)
left join
(
select
ordinazioni.id_transportu as transid,
sum(ordinazioni.koszt_tran sportu) 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_TR ANSPORT.tr ansid)
left join
( select
ordinazioni.id as idord,
coalesce(sum(posizioni.val ore),0.00) as w
from
ordinazioni
left join posizioni on ordinazioni.id=posizioni.i d_ordinazi one
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(specyfi kacja.widt h*specyfik acja.heigh t*specyfik acja.ilosc *0.000001) ,2),0.00) as m2,
coalesce(round(sum(specyfi kacja.widt h*specyfik acja.heigh t*specyfik acja.ilosc *0.000001) *asortymen t.waga_m2, 2),0.00) as Kg
from
ordinazioni
left join posizioni on (ordinazioni.id=posizioni. id_ordinaz ione and posizioni.asortyment=6)
left join specyfikacja on (posizioni.id=specyfikacja .posizioni _id)
left join asortyment on (posizioni.asortyment=asor tyment.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(specyfi kacja.widt h*specyfik acja.heigh t*specyfik acja.ilosc *0.000001) ,2),0.00) as m2,
coalesce(round(sum(specyfi kacja.widt h*specyfik acja.heigh t*specyfik acja.ilosc *0.000001) *asortymen t.waga_m2, 2),0.00) as Kg
from
ordinazioni
left join posizioni on (ordinazioni.id=posizioni. id_ordinaz ione and posizioni.asortyment=7)
left join specyfikacja on (posizioni.id=specyfikacja .posizioni _id)
left join asortyment on (posizioni.asortyment=asor tyment.id )
where ordinazioni.id>700
group by idord
) OALU on ordinazioni.id=OALU.idord
left join
(
select
ordinazioni.id as idord,
coalesce(sum(pagamenti.imp orto),0.00 ) as suma
from
ordinazioni
left join ordinazioni_pagamenti on (ordinazioni.id=ordinazion i_pagament i.id_ordin azione)
left join pagamenti on (ordinazioni_pagamenti.id_ pagamento= pagamenti. id)
where
ordinazioni.id>700
group by idord
) ZAPLACONO on ordinazioni.id=ZAPLACONO.i dord
where ordinazioni.id>700
group by ordinazioni.id
having ((to_days(max(posizioni.da ta_merce_a _magazzino ))-to_days (curdate() )) < 14) and stat<>4
order by `C.A.P.` asc;
") or show_erx(1);
$transporty=mysql_query("
select
ordinazioni.id as IDORD,
concat(\"<b>\",left(ordina
coalesce(concat(\"[\",tran
ordinazioni.cap as \"C.A.P.\",
left(ordinazioni.miasto_do
ordinazioni.koszt_transpor
if (to_days(max(posizioni.dat
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
coalesce(kolory.kolor,0) as kolor,
coalesce(max(posizioni.dat
from
ordinazioni
left join posizioni on ordinazioni.id=posizioni.i
left join transporty on (ordinazioni.id_transportu
left join
( select
transporty.id as transid,
coalesce(round(sum(specyfi
from
transporty,
ordinazioni
left join posizioni on (posizioni.id_ordinazione=
left join asortyment on (posizioni.asortyment=asor
left join specyfikacja on (posizioni.id=specyfikacja
where
ordinazioni.id_transportu=
and transporty.status<>4
and ordinazioni.id>700
group by transid
) ZALADOWANO on (ordinazioni.id_transportu
left join
( select
transporty.id as transid,
coalesce(sum(specyfikacja.
from
transporty,
ordinazioni
left join posizioni on (posizioni.id_ordinazione=
left join asortyment on (posizioni.asortyment=asor
left join specyfikacja on (posizioni.id=specyfikacja
where
ordinazioni.id_transportu=
and transporty.status<>4
and ordinazioni.id>700
group by transid
) ZALADOWANO_OKIEN on (ordinazioni.id_transportu
left join
( select
transporty.id as transid,
coalesce(sum(specyfikacja.
from
transporty,
ordinazioni
left join posizioni on (posizioni.id_ordinazione=
left join asortyment on (posizioni.asortyment=asor
left join specyfikacja on (posizioni.id=specyfikacja
where
ordinazioni.id_transportu=
and transporty.status<>4
and ordinazioni.id>700
group by transid
) ZALADOWANO_OKIENNIC on (ordinazioni.id_transportu
left join statusy_transportu on (transporty.status=statusy
left join kolory on (transporty.kolor=kolory.i
left join
(
select
ordinazioni.id_transportu as transid,
sum(ordinazioni.koszt_tran
from
ordinazioni,transporty
where
ordinazioni.id_transportu=
and
ordinazioni.id_transportu<
and
transporty.status<>4
and
ordinazioni.id>700
group by
transid
) OPLATA_TRANSPORT on (ordinazioni.id_transportu
left join
( select
ordinazioni.id as idord,
coalesce(sum(posizioni.val
from
ordinazioni
left join posizioni on ordinazioni.id=posizioni.i
where ordinazioni.id>700
group by idord
) V on ordinazioni.id=V.idord
left join
(
select ordinazioni.id as idord,
coalesce(sum(specyfikacja.
coalesce(round(sum(specyfi
coalesce(round(sum(specyfi
from
ordinazioni
left join posizioni on (ordinazioni.id=posizioni.
left join specyfikacja on (posizioni.id=specyfikacja
left join asortyment on (posizioni.asortyment=asor
where ordinazioni.id>700
group by idord
) SPCV on ordinazioni.id=SPCV.idord
left join
(
select ordinazioni.id as idord,
coalesce(sum(specyfikacja.
coalesce(round(sum(specyfi
coalesce(round(sum(specyfi
from
ordinazioni
left join posizioni on (ordinazioni.id=posizioni.
left join specyfikacja on (posizioni.id=specyfikacja
left join asortyment on (posizioni.asortyment=asor
where ordinazioni.id>700
group by idord
) OALU on ordinazioni.id=OALU.idord
left join
(
select
ordinazioni.id as idord,
coalesce(sum(pagamenti.imp
from
ordinazioni
left join ordinazioni_pagamenti on (ordinazioni.id=ordinazion
left join pagamenti on (ordinazioni_pagamenti.id_
where
ordinazioni.id>700
group by idord
) ZAPLACONO on ordinazioni.id=ZAPLACONO.i
where ordinazioni.id>700
group by ordinazioni.id
having ((to_days(max(posizioni.da
order by `C.A.P.` asc;
") or show_erx(1);
ASKER
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?
ASKER
No ideas? Nobody can help me?
ASKER
Nobody knows?
ASKER
Why nobody can help me?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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...
"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...
ASKER
Is there a way to optimize the query? Nobody knows?
ASKER
Hi!
You helped me in some way :-) but I must put a new question now...
You helped me in some way :-) but I must put a new question now...
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
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
ASKER
Hi Johnsone!
Thanks for the help but this solution does not work with multiple sums...
Thanks for the help but this solution does not work with multiple sums...
It should because at the outermost query, you are still grouping by only 1 set of criteria.
ASKER
hmmm I will try :-) and I let you know ...
ASKER
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_ordinaz ione)
left join ordinazioni_pagamenti on ordinazioni.id=ordinazioni _pagamenti .id_ordina zione
left join pagamenti on ordinazioni_pagamenti.id_p agamento=p agamenti.i d
group by ordinazioni.id;
The resulting sums are WRONG
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.
left join ordinazioni_pagamenti on ordinazioni.id=ordinazioni
left join pagamenti on ordinazioni_pagamenti.id_p
group by ordinazioni.id;
The resulting sums are WRONG
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.