List:General Discussion« Previous MessageNext Message »
From:Cory Date:May 29 2006 6:07am
Subject:ugly SQL for a report...
View as plain text  
I have the following query that is running VERY slowly.   Anyone have 
any suggestions?

---
SELECT pnr.ID ID_pnr, pnr.reservationdatetime, pnr.conf_number, 
pnr.created_by, GROUP_CONCAT(pp.name_last,', ',pp.name_first ORDER BY 
name_last DESC SEPARATOR '<br>') names, (SELECT SUM(pf.base_fare*(SELECT 
COUNT(1) FROM pnr_passengers pp WHERE pp.ID_pnr=pnr.ID )) FROM pnr_fares 
pf WHERE pf.ID_pnr=pnr.ID ) base_fare, (SELECT SUM(pf.tax*(SELECT 
COUNT(1) FROM pnr_passengers pp WHERE pp.ID_pnr=pnr.ID )) FROM pnr_fares 
pf WHERE pf.ID_pnr=pnr.ID ) tax , (SELECT SUM(psf.segfee_amount*(SELECT 
COUNT(1) FROM pnr_passengers pp WHERE pp.ID_pnr=pnr.ID )) FROM 
pnr_segments_fees psf INNER JOIN pnr_segments pss ON 
psf.ID_segments=pss.ID WHERE psf.segfee_code='XF' AND pss.ID_pnr=pnr.ID 
) XF ,(SELECT SUM(amount_value) service_fees FROM pnr_service_fees WHERE 
ID_pnr=pnr.ID) services ,(SELECT SUM(amount) FROM pnr_payments_credits 
WHERE ID_pnr=pnr.ID AND bln_payment='1' AND transaction_type='sale') 
payments, (SELECT SUM(amount) FROM pnr_payments_credits WHERE 
ID_pnr=pnr.ID AND bln_payment='1' AND transaction_type='credit') credits 
FROM pnr INNER JOIN pnr_passengers pp ON pnr.ID=pp.ID_pnr WHERE 
pnr.reservationdatetime>='2000-05-29 00:00:00' AND 
pnr.reservationdatetime<='2006-05-29 23:59:59' GROUP BY pnr.ID ORDER BY 
reservationdatetime;
---

I can see that one issue is the repeated use of SELECT COUNT...   Is 
there any way to get the query to only do this once?

ANY help will be greatly appreciated.

We're running MySQL 5

Cory.


Thread
ugly SQL for a report...Cory29 May
  • Re: ugly SQL for a report...Jochem van Dieten29 May
  • Re: ugly SQL for a report...Cory Robin29 May
    • Re: ugly SQL for a report...Jochem van Dieten29 May
      • Re: ugly SQL for a report...Cory Robin30 May
    • Re: ugly SQL for a report...Jochem van Dieten29 May
    • Re: ugly SQL for a report...Jochem van Dieten29 May
    • Re: ugly SQL for a report...Peter Brawley30 May