Solved

MySql query optimization

Posted on 2010-11-29
20
449 Views
Last Modified: 2012-06-21
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


0
Comment
Question by:mlattari
  • 13
  • 5
20 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34232581
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
 
LVL 34

Expert Comment

by:johnsone
ID: 34232625
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
 
LVL 34

Expert Comment

by:johnsone
ID: 34232641
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
 

Author Comment

by:mlattari
ID: 34232666
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
 

Author Comment

by:mlattari
ID: 34232778
Hmmm nobody knows? :-(
0
 

Author Comment

by:mlattari
ID: 34236823
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
 

Author Comment

by:mlattari
ID: 34238155

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
 

Author Comment

by:mlattari
ID: 34240446
No ideas? Nobody can help me?
0
 

Author Comment

by:mlattari
ID: 34240954
Nobody knows?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:mlattari
ID: 34246659
Why nobody can help me?
0
 
LVL 34

Accepted Solution

by:
johnsone earned 500 total points
ID: 34252851
I would use the method that I posted to move all your subqueries to joins.  That may help.
0
 

Author Comment

by:mlattari
ID: 34252933
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
 

Author Comment

by:mlattari
ID: 34253268
Is there a way to optimize the query? Nobody knows?
0
 

Author Closing Comment

by:mlattari
ID: 34256867
Hi!

You helped me in some way :-)  but I must put a new question now...
0
 
LVL 34

Expert Comment

by:johnsone
ID: 34257810
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
 

Author Comment

by:mlattari
ID: 34258163
Hi Johnsone!

Thanks for the help but this solution does not work with multiple sums...
0
 
LVL 34

Expert Comment

by:johnsone
ID: 34259121
It should because at the outermost query, you are still grouping by only 1 set of criteria.
0
 

Author Comment

by:mlattari
ID: 34259455
hmmm I will try :-) and I let you know ...
0
 

Author Comment

by:mlattari
ID: 34265713
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now