On 5/29/06, Cory wrote:
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;
That is indeed very ugly. If you prettify it by adding proper
indenting etc. I might be inclined to read it and think about an
answer.
Jochem
